Finding out Null valued columns in a database

USE [Database name]

DECLARE @colName VARCHAR(200)
DECLARE @tabName VARCHAR(200)

DECLARE @nullTable  TABLE
(TABLE_NAME  varchar(200),
COLUMN_NAME  varchar(200),
NOTNULLCOUNT  int)

DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT  T.NAME , C.NAME
FROM SYS.OBJECTS AS T
JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC='USER_TABLE';

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @tabName,@colName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Count AS INT
DECLARE @SQLString nvarchar(500)
SET @SQLString =N'SELECT '''+@tabName+''','''+@colName+''',count(*) FROM dbo.['+@tabName+'] WHERE  ['+@colName+'] IS NOT NULL'
PRINT @SQLString
INSERT @nullTable EXEC (@SQLString)


FETCH NEXT FROM @MyCursor
INTO @tabName,@colName
END
CLOSE @MyCursor
DEALLOCATE @MyCursor


SELECT * FROM @nullTable WHERE NOTNULLCOUNT=0 ORDER BY TABLE_NAME,COLUMN_NAME

Extreme wait-time when taking a SQL Server database offline

When it takes long time to make a database offline try with following sql.

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE

If it does not help, kill the session.

First find out the spid using following command.

EXEC sp_who2

KILL <SPID>

Database mode change between Single user and multimode user

USE MASTER;


ALTER DATABASE [wealthstore] SET MULTI_USER
go


ALTER DATABASE [wealthstore] SET SINGLE_USER
GO

Database Mail configuration

1. Open the SSMS and right click on the Database Mail in Management portion. Click on the Configure Database Mail.

2. Click Next on the wizerd.

3. Select Set up Database Mail by performing the following tasks and click on the Next.

4. Type Profile name and select Add.. button.

 5. Click on New Account.

6. Type Account name, Email address, Display name, Reply e-mail and Server name. Then click ok.

7. Select created profile name Default. Select Next.

8. Test default database mail by executing following store procedure.

EXEC msdb.dbo.sp_send_dbmail 

@recipients = 'parvez.nawaz@gamil.com',

    @subject = 'Test mail from UAT',

    --@profile_name = 'AMS Development',

    @query = 'select getdate()',

    @attach_query_result_as_file = 0

List all the tables in a database

SELECT * FROM sysobjects WHERE xtype='U'

Here is a list of other object types you can search for as well:

• C: Check constraint
• D: Default constraint
• F: Foreign Key constraint
• L: Log
• P: Stored procedure
• PK: Primary Key constraint
• RF: Replication Filter stored procedure
• S: System table
• TR: Trigger
• U: User table
• UQ: Unique constraint
• V: View
• X: Extended stored procedure

In SQL Server 2012, you can use

SELECT * FROM information_schema.tables where table_type='BASE TABLE'

You can also use

select * from sys.tables

Adding new assembly and functions

1. Check if there is any previous assembly exists. If there is any, then remove it.

SELECT
        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class,
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class

2. Copy dll file to server (eg: F:\Parvez\Msdn.SqlRegex.dll')

3. Create assembly in database.

                    create assembly "Msdn.SqlRegex" from 'F:\Parvez\Msdn.SqlRegex.dll'

4. Create sql object using that assembly.
   
USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexMatch]    Script Date: 10/28/2014 16:34:10 ******/
CREATE FUNCTION [dbo].[RegexMatch](@input [nvarchar](max), @pattern [nvarchar](4000))
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Msdn.SqlRegex].[UserDefinedFunctions].[RegexMatch]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatch'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegexMatch.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatch'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=11 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatch'


USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexGroup]    Script Date: 10/28/2014 16:34:48 ******/
CREATE FUNCTION [dbo].[RegexGroup](@input [nvarchar](max), @pattern [nvarchar](4000), @name [nvarchar](4000))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Msdn.SqlRegex].[UserDefinedFunctions].[RegexGroup]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroup'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegexGroup.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroup'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=9 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroup'

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexGroups]    Script Date: 10/28/2014 16:35:31 ******/
CREATE FUNCTION [dbo].[RegexGroups](@input [nvarchar](max), @pattern [nvarchar](4000))
RETURNS  TABLE (
[Index] [int] NULL,
[Group] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Text] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Msdn.SqlRegex].[UserDefinedFunctions].[RegexGroups]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroups'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegexGroups.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroups'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=80 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroups'



USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexMatches]    Script Date: 10/28/2014 16:36:02 ******/
CREATE FUNCTION [dbo].[RegexMatches](@input [nvarchar](max), @pattern [nvarchar](4000))
RETURNS  TABLE (
[Index] [int] NULL,
[Text] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Msdn.SqlRegex].[UserDefinedFunctions].[RegexMatches]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatches'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegexMatches.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatches'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=61 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatches'

5. Check created objects

   SELECT

        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class,
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class

List all the objects associated with their assembly name

SELECT
        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class,
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class

Dropping all views, stored procedure and functions

Dropping all the views

1. Get all the views

select name from sys.objects where type='v' order by name

2. Drop all the views

DECLARE @viewName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type in ('V')
      OPEN cur

      FETCH NEXT FROM cur INTO @viewName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP VIEW ' + @viewName)
Select @viewName
            FETCH NEXT FROM cur INTO @viewName
      END
      CLOSE cur
      DEALLOCATE cur

Dropping all the stored procedures

1. Display all the stored procedure.

Select name from sys.objects where type='p' order by name

2. Drop all the stored procedure

DECLARE @viewName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type in ('P')
      OPEN cur

      FETCH NEXT FROM cur INTO @viewName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP PROCEDURE ' + @viewName)
Select @viewName
            FETCH NEXT FROM cur INTO @viewName
      END
      CLOSE cur
      DEALLOCATE cur

Dropping all the functions

1. Display all the existing functions

 select name from sys.objects where type='TF' or type='IF' or type='FN' order by name

2. Drop all the existing functions

DECLARE @viewName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type in ('TF','IF','FN')
      OPEN cur

      FETCH NEXT FROM cur INTO @viewName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP Function ' + @viewName)
Select @viewName
            FETCH NEXT FROM cur INTO @viewName
      END
      CLOSE cur
      DEALLOCATE cur

List all jobs with specified stored procedure in their steps

USE [msdb]
GO
SELECT j.job_id,
s.srvname,
j.name,
js.step_id,
js.command,
j.enabled
FROM dbo.sysjobs j
JOIN dbo.sysjobsteps js
ON js.job_id = j.job_id
JOIN master.dbo.sysservers s
ON s.srvid = j.originating_server_id
WHERE js.command LIKE N'%spAddContractorToBCL%'
GO

List all the jobs with ssis package

USE [msdb]
GO
SELECT
   Srv.srvname AS ServerName,
   Job.name AS JobName,
   JStep.step_id,
   JStep.step_name AS StepName,
   JStep.command,
   Job.enabled
FROM   dbo.sysjobs Job
JOIN   dbo.sysjobsteps JStep
   ON  JStep.job_id = Job.job_id
JOIN   MASTER.dbo.sysservers Srv
   ON  Srv.srvid = Job.originating_server_id
WHERE  JStep.subsystem='SSIS' AND enabled=1

ORDER BY Job.name,step_id

List all the jobs of sql server database

SELECT name AS [Job Name],CASE WHEN enabled=1 THEN 'YES' ELSE 'NO' END AS ENABLED,
description AS Description
FROM msdb.dbo.sysjobs
ORDER BY enabled,description

List all user permission to all databases

DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)

INSERT @DB_USers
EXEC sp_MSforeachdb

'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT

dbname,username ,logintype ,create_date ,modify_date ,

STUFF(

(

SELECT ',' + CONVERT(VARCHAR(500),associatedrole)

FROM @DB_USers user2

WHERE

user1.DBName=user2.DBName AND user1.UserName=user2.UserName

FOR XML PATH('')

)

,1,1,'') AS Permissions_user

FROM @DB_USers user1

GROUP BY

dbname,username ,logintype ,create_date ,modify_date

ORDER BY DBName,username

Find active connections and kill

Find active connections

SELECT DB_NAME(dbid) as DBName, dbid,loginame as LoginName,CAST(SPID AS VARCHAR(4)) AS spid
FROM     sys.sysprocesses WHERE dbid>10
ORDER BY dbid           

You will find spid. Then kill the connection by spid. Do not kill yourself.

KILL 61

List all the function name for a database

connect to any database.
Use [Database name]


SELECT name AS function_name,type_desc
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%'

MSSQL: List all table size in a database

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN    
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    t.Name

MSSQL: Creating Link Database Server

You can create any link database server with any provider string.

EXEC master.dbo.sp_addlinkedserver

    @server = N'LZAMSMEXPRD\AMSMEX',

    @srvproduct=N'MSSQL',

    @provider=N'SQLNCLI',

    @provstr=N'PROVIDER=SQLOLEDB;SERVER=lzamsmexqat\amsmex,1433'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LZAMSMEXPRD\AMSMEX',

    @useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='P@ssword!'

Eariler version could have some issue with user having no sysadmin role. You can use the following one for that.

EXEC master.dbo.sp_addlinkedserver

    @server = N'CHRGBKP2NT',

    @srvproduct=N'MSSQL',

    @provider=N'SQLNCLI',

@datasrc='PSM-TESTSVR2'


EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CHRGBKP2NT',
    @useself=N'False',@locallogin=NULL,@rmtuser=N'jcolton',@rmtpassword='jcolton254'


For SQL Server 2012, use the following script.

EXEC master.dbo.sp_addlinkedserver @server = N'TOCGJTIM1PV\JTIME', @srvproduct=N'MSSQL', @provider=N'SQLNCLI', @datasrc=N'TOCFJTIM1UV\JTIME'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TOCGJTIM1PV\JTIME',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

List MSSQL Database size

SELECT
    DB_NAME(db.database_id) DatabaseName,
    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
    (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
    (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
    LEFT JOIN
   (
   SELECT database_id, SUM(size) RowSize FROM sys.master_files
   WHERE type = 0
   GROUP BY database_id, type
   ) mfrows
   ON mfrows.database_id = db.database_id
   LEFT JOIN
  (
  SELECT database_id, SUM(size) LogSize FROM sys.master_files
  WHERE type = 1
  GROUP BY database_id, type
   ) mflog
  ON mflog.database_id = db.database_id
  LEFT JOIN
  (
  SELECT database_id, SUM(size) StreamSize
  FROM sys.master_files
  WHERE type = 2
  GROUP BY database_id, type
  ) mfstream
  ON mfstream.database_id = db.database_id
  LEFT JOIN
  (
   SELECT database_id, SUM(size) TextIndexSize
   FROM sys.master_files
   WHERE type = 4
   GROUP BY database_id, type
   ) mftext
   ON mftext.database_id = db.database_id

       MSSQL: How to recovery Database from SUSPECT Mode

Database generally Database moves to SUSPECT State due to following Reasons

• Database is corrupted
• ldf file is corrupted
• Disk Hardware Failure or Lack of Disk Space
• SQL Server does not have sufficient memory to Start SQL Server

Step 1

Never detach a suspect database it end up without re attach .as it is a Suspect database we can’t
take Backup of it. take the database into EMERGENCY MODE

– Take the database into Emergency mode
ALTER DATABASE [Database Name] SET EMERGENCY
GO

Step 2

set the database into single user mode using
– Change into single user mode
ALTER DATABASE [Database Name] SET SINGLE_USER

Step 3

Run DBCC Checkdb with allow data loss, by using this command all the open transactions will
be deleted ,
– Then run DBCC CheckDB command. Warning! REPAIR_ALLOW_DATA_LOSS can cause
some data to be lost!.
DBCC CheckDB (<[Database Name]> , REPAIR_ALLOW_DATA_LOSS)

Step 4

when the database repair is done set the database in multi user mode using

– Change from multiuser

ALTER DATABASE dbName SET MULTI_USER

Altogether

ALTER DATABASE ReportServer SET EMERGENCY
ALTER DATABASE ReportServer SET SINGLE_USER
DBCC CheckDB (ReportServer , REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE ReportServer SET MULTI_USER