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

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

Reseeding Identity

After truncate, identity becomes 1 by default.

TRUNCATE TABLE dbo.Customer

You can check by

SELECT IDENT_CURRENT('dbo.Customer')


It is possible to reseed to different value. For example 4

DBCC CHECKIDENT('dbo.Customer',RESEED,4)


Check again.

SELECT IDENT_CURRENT('dbo.Customer')

Difference between Delete and Truncate

The TRUNCATE statement deletes all rows from the target table. Unlike the DELETE statement,
it doesn’t have an optional filter, so it’s all or nothing. As an example, the following statement
truncates the table Sales.MyOrderDetails.

TRUNCATE TABLE Sales.MyOrderDetails;

After executing the statement, the target table is empty.

The DELETE and TRUNCATE statements have a number of important differences between
them:

1. The DELETE statement writes significantly more to the transaction log compared to the TRUNCATE statement. For DELETE, SQL Server records in the log the actual data that was deleted. For TRUNCATE, SQL Server records information only about which pages were deallocated. As a result, the TRUNCATE statement tends to be substantially faster.
2. The DELETE statement doesn’t attempt to reset an identity property if one is associated with a column in the target table. The TRUNCATE statement does. If you use TRUNCATE and would prefer not to reset the property, you need to store the current identity value plus one in a variable (using the IDENT_CURRENT function), and reseed the property with the stored value after the truncation.
3. The DELETE statement is supported if there’s a foreign key pointing to the table in question as long as there are no related rows in the referencing table. TRUNCATE is not allowed if a foreign key is pointing to the table—even if there are no related rows in the referencing table, and even if the foreign key is disabled.
4. The DELETE statement is allowed against a table involved in an indexed view. A TRUNCATE statement is disallowed in such a case.
5. The DELETE statement requires DELETE permissions on the target table. The TRUNCATE statement requires ALTER permissions on the target table.

When you need to delete all rows from a table, it is usually preferred to use TRUNCATE
because it is significantly faster than DELETE. However, it does require stronger permissions,
and is more restricted.

Granting a access to SSIS server

To grant access to the Integration Services service

1. Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry.
2. In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node.
3. Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.
4. On the Security tab, click Edit in the Launch and Activation Permissions area.
5. Add users and assign appropriate permissions, and then click Ok.
6. Repeat steps 4 - 5 for Access Permissions.
7. Restart SQL Server Management Studio.
8. Restart the Integration Services Service.
           a) On the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.
   
          b) In the SQL Server Configuration Manager snap-in, locate SQL Server Integration Services in the list of services, right-click SQL Server Integration Services, and then click Restart. 
9. Account might require sysdba role, if the following error shows up.
   "The Execute permission was denied on the object 'sp_ssis_listfolders'.

Download all .rdl from SSRS server

1. BCP utility should be enabled first.

-- Allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO

-- Update the currently configured value for advanced options.
RECONFIGURE
GO

-- Enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO

-- Update the currently configured value for xp_cmdshell
RECONFIGURE
GO

-- Disallow further advanced options to be changed.
EXEC sp_configure 'show advanced options', 0
GO

-- Update the currently configured value for advanced options.
RECONFIGURE
GO

2. Change the @OutputPath variable and execute it.

--Replace NULL with keywords of the ReportManager's Report Path, 

--if reports from any specific path are to be downloaded

DECLARE @FilterReportPath AS VARCHAR(500) = NULL 

--Replace NULL with the keyword matching the Report File Name,

--if any specific reports are to be downloaded

DECLARE @FilterReportName AS VARCHAR(500) = NULL

--Replace this path with the Server Location where you want the

--reports to be downloaded..

DECLARE @OutputPath AS VARCHAR(500) = 'D:\Reports\Download\'

--Used to prepare the dynamic query

DECLARE @TSQL AS NVARCHAR(MAX)

--Reset the OutputPath separator.

SET @OutputPath = REPLACE(@OutputPath,'\','/')

--Simple validation of OutputPath; this can be changed as per ones need.

IF LTRIM(RTRIM(ISNULL(@OutputPath,''))) = ''

BEGIN

  SELECT 'Invalid Output Path'

END

ELSE

BEGIN

   --Prepare the query for download.

   /*

   Please note the following points -

   1. The BCP command could be modified as per ones need. E.g. Providing UserName/Password, etc.

   2. Please update the SSRS Report Database name. Currently, it is set to default - [ReportServer]

   3. The BCP does not create missing Directories. So, additional logic could be implemented to handle that.

   4. SSRS stores the XML items (Report RDL and Data Source definitions) using the UTF-8 encoding. 

      It just so happens that UTF-8 Unicode strings do not NEED to have a BOM and in fact ideally would not have one. 

 However, you will see some report items in your SSRS that begin with a specific sequence of bytes (0xEFBBBF). 

 That sequence is the UTF-8 Byte Order Mark. It’s character representation is the following three characters, “”. 

      While it is supported, it can cause problems with the conversion to XML, so it is removed.

   */

   SET @TSQL = STUFF((SELECT

                      ';EXEC master..xp_cmdshell ''bcp " ' +

                      ' SELECT ' +

                      ' CONVERT(VARCHAR(MAX), ' +

                      '       CASE ' +

                      '         WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''''''') '+

                      '         ELSE C.Content '+

                      '       END) ' +

 ' FROM ' +

 ' [ReportServer].[dbo].[Catalog] CL ' +

 ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' +

 ' WHERE ' +

 ' CL.ItemID = ''''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''' " queryout "' + @OutputPath + '' + CL.Name + '.rdl" ' + '-T -c -x'''

                    FROM

[ReportServer].[dbo].[Catalog] CL

WHERE

CL.[Type] = 2 --Report

AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/')

AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%', CL.Name)

FOR XML PATH('')), 1,1,'')

 --SELECT @TSQL

--Execute the Dynamic Query

EXEC SP_EXECUTESQL @TSQL

END

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 of all SSRS reports name and their datasource

SELECT
    DS.Name AS DatasourceName,
    C.Name AS DependentItemName,
    C.Path AS DependentItemPath
FROM
    ReportServer.dbo.Catalog AS C
        INNER JOIN
    ReportServer.dbo.Users AS CU
        ON C.CreatedByID = CU.UserID
        INNER JOIN
    ReportServer.dbo.Users AS MU
        ON C.ModifiedByID = MU.UserID
        LEFT OUTER JOIN
    ReportServer.dbo.SecData AS SD
        ON C.PolicyID = SD.PolicyID AND SD.AuthType = 1
        INNER JOIN
    ReportServer.dbo.DataSource AS DS
        ON C.ItemID = DS.ItemID
WHERE
    DS.Name IS NOT NULL
ORDER BY
    DS.Name;

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

Disable all the SSRS Reporting services' Subscription Jobs

1. List all reporting service subscription jobs.

USE ReportServer
SELECT     Schedule.ScheduleID AS SQLAgent_Job_Name, Subscriptions.Description AS sub_desc, Subscriptions.DeliveryExtension AS sub_delExt,
                      [Catalog].Name AS ReportName, [Catalog].Path AS ReportPath,'exec msdb.dbo.sp_update_job @job_name = '''+CAST(Schedule.ScheduleID AS VARCHAR(max))+''',@enabled = 0' AS sqlcmd
FROM         ReportSchedule INNER JOIN
                      Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID INNER JOIN
                      Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID INNER JOIN
                      [Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID



2. Disable each jobs.

exec msdb.dbo.sp_update_job @job_name = 'A83AE658-E203-457B-83D3-5715EA64A47A',@enabled = 0

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

SSIS Package batch export and import

1.

You can create batch export command by following sql.

select 'dtutil /SQL "' +[p].[name] + '" /ENCRYPT FILE;"F:\SSISPackages\'+[p].[name]+'.dtsx;1"'
from msdb.dbo.sysdtspackages90 [p]

Output command will be like this.

   dtutil /SQL "SSIS_Package_Name" /ENCRYPT FILE;"F:\SSISPackages\SSIS_Package_Name.dtsx;1"

Here 1 means, copy all the sensitive data with user key.
Here we are exporting SSIS packages from SQL server to F:\SSISPackages directory.

Run these batch commands in Souce SSIS server's command prompt.

Copy all the .dtsx file from source to destination server.

2.
Batch SSIS packages import commands can be generated by following sql

select 'dtutil /FILE "C:\Users\s5142805\Downloads\SSIS pkg\' +[p].[name] + '.dtsx" /COPY SQL;"/'+[p].[name]+'"'
from msdb.dbo.sysdtspackages90 [p]

Output will be like this.

dtutil /FILE "C:\Users\s5142805\Downloads\SSIS pkg\ABMCashOut - Import v2.dtsx" /COPY SQL;"/ABMCashOut"

Here we are importing SSIS packages from C:\Users\s5142805\Downloads\SSIS pkg\ to SQL server directory.

Run these batch commands in Destination SSIS server's command prompt.