Reorganize all indexes in a Database in SQL Server.

Index Maintenance is one of the major task for a DBA. If rate of Index fragmentation increased then index de-fragmentation is become required. So we can use index reorganize option to rebuild all the indexes. Use Index reorganize option when rate of index fragmentation is b/w 10% to  40%.

Syntax:

Alter Index All On Table_Name Reorganize.

Example:

ALTER INDEX ALL ON geographicals Reorganize

GO

 

Rebuild Index for All tablex in database:

Exec sp_msforeachtable 'ALTER INDEX ALL ON ? Reorganize'

GO

Reference: 

SQL SERVER – Drop All the Foreign Key Constraint in Database – Create All the Foreign Key Constraint in Database

SET NOCOUNT ON

DECLARE @table TABLE (
 RowId INT PRIMARY KEY IDENTITY(1, 1)
 ,ForeignKeyConstraintName NVARCHAR(200)
 ,ForeignKeyConstraintTableSchema NVARCHAR(200)
 ,ForeignKeyConstraintTableName NVARCHAR(200)
 ,ForeignKeyConstraintColumnName NVARCHAR(200)
 ,PrimaryKeyConstraintName NVARCHAR(200)
 ,PrimaryKeyConstraintTableSchema NVARCHAR(200)
 ,PrimaryKeyConstraintTableName NVARCHAR(200)
 ,PrimaryKeyConstraintColumnName NVARCHAR(200)
 )

INSERT INTO @table (
 ForeignKeyConstraintName
 ,ForeignKeyConstraintTableSchema
 ,ForeignKeyConstraintTableName
 ,ForeignKeyConstraintColumnName
 )
SELECT U.CONSTRAINT_NAME
 ,U.TABLE_SCHEMA
 ,U.TABLE_NAME
 ,U.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE @table
SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE @table
SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA
 ,PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE @table
SET PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

--SELECT * FROM @table
--DROP CONSTRAINT:
SELECT '
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '
 
GO'
FROM @table

--ADD CONSTRAINT:
SELECT '
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')
 
GO'
FROM @table
GO






Reference:https://blog.sqlauthority.com/2014/04/11/sql-server-drop-all-the-foreign-key-constraint-in-database-create-all-the-foreign-key-constraint-in-database/

List all Orphan users in a SQL Server database

CREATE TABLE ##ORPHANUSER (
DBNAME VARCHAR(100)
,USERNAME VARCHAR(100)
,CREATEDATE VARCHAR(100)
,USERTYPE VARCHAR(100)
)
EXEC SP_MSFOREACHDB ' USE [?]
INSERT INTO ##ORPHANUSER
SELECT DB_NAME() DBNAME, NAME,CREATEDATE,
(CASE 
WHEN ISNTGROUP = 0 AND ISNTUSER = 0 THEN ''SQL LOGIN''
WHEN ISNTGROUP = 1 THEN ''NT GROUP''
WHEN ISNTGROUP = 0 AND ISNTUSER = 1 THEN ''NT LOGIN''
END) [LOGIN TYPE] FROM sys.sysusers
WHERE SID IS NOT NULL AND SID <> 0X0 AND ISLOGIN =1 AND
SID NOT IN (SELECT SID FROM sys.syslogins)'
SELECT *
FROM ##ORPHANUSER
DROP TABLE ##ORPHANUSER

List out all MDF and LDF File locations of SQL Sever Databases

SELECT
    db.name AS DBName,
    type_desc AS FileType,
    Physical_Name AS Location
FROM
    sys.master_files mf
INNER JOIN
    sys.databases db ON db.database_id = mf.database_id

List of all user databases in SQL Server

SELECT *
FROM   master.sys.databases
WHERE  Cast(CASE WHEN name IN ('master', 'model', 'msdb', 'tempdb') THEN 1 ELSE is_distributor END As bit) = 0

SQL Script to find all Date/time columns in database

select
    so.name table_name
   ,sc.name column_name
   ,st.name data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('DATETIME', 'DATE', 'TIME')

Generate Script of All indexes in a SQL Server database

SELECT ' CREATE ' +
       CASE
            WHEN I.is_unique = 1 THEN ' UNIQUE '
            ELSE ''
       END +
       I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +
       I.name + ' ON ' +
       SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +
       KeyColumns + ' )  ' +
       ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
       ISNULL(' WHERE  ' + I.filter_definition, '') + ' WITH ( ' +
       CASE
            WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
            ELSE ' PAD_INDEX = OFF '
       END + ',' +
       'FILLFACTOR = ' + CONVERT(
           CHAR(5),
           CASE
                WHEN I.fill_factor = 0 THEN 100
                ELSE I.fill_factor
           END
       ) + ',' +
       -- default value
       'SORT_IN_TEMPDB = OFF ' + ',' +
       CASE
            WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
            ELSE ' IGNORE_DUP_KEY = OFF '
       END + ',' +
       CASE
            WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
            ELSE ' STATISTICS_NORECOMPUTE = ON '
       END + ',' +
       ' ONLINE = OFF ' + ',' +
       CASE
            WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
            ELSE ' ALLOW_ROW_LOCKS = OFF '
       END + ',' +
       CASE
            WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
            ELSE ' ALLOW_PAGE_LOCKS = OFF '
       END + ' ) ON [' +
       DS.name + ' ] ' +  CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]
FROM   sys.indexes I
       JOIN sys.tables T
            ON  T.object_id = I.object_id
       JOIN sys.sysindexes SI
            ON  I.object_id = SI.id
            AND I.index_id = SI.indid
       JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , ' + C.name + CASE
                                                                       WHEN MAX(CONVERT(INT, IC1.is_descending_key))
                                                                            = 1 THEN
                                                                            ' DESC '
                                                                       ELSE
                                                                            ' ASC '
                                                                  END
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column =
                                                          0
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id
                                          ORDER BY
                                                 MAX(IC1.key_ordinal)
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) KeyColumns
                           FROM   sys.index_columns IC2
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp3
            )tmp4
            ON  I.object_id = tmp4.object_id
            AND I.Index_id = tmp4.index_id
       JOIN sys.stats ST
            ON  ST.object_id = I.object_id
            AND ST.stats_id = I.index_id
       JOIN sys.data_spaces DS
            ON  I.data_space_id = DS.data_space_id
       JOIN sys.filegroups FG
            ON  I.data_space_id = FG.data_space_id
       LEFT JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , ' + C.name
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column =
                                                          1
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) IncludedColumns
                           FROM   sys.index_columns IC2
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp1
                WHERE  IncludedColumns IS NOT NULL
            ) tmp2
            ON  tmp2.object_id = I.object_id
            AND tmp2.index_id = I.index_id
WHERE  I.is_primary_key = 0
       AND I.is_unique_constraint = 0
           --AND I.Object_id = object_id('Person.Address') --Comment for all tables
           --AND I.name = 'IX_Address_PostalCode' --comment for all indexes 

Query to List all the partitioned tables in SQL Server database

select object_schema_name(i.object_id) as [schema],
    object_name(i.object_id) as [object],
    i.name as [index],
    s.name as [partition_scheme]
    from sys.indexes i
    join sys.partition_schemes s on i.data_space_id = s.data_space_id order by [schema]

Delete all Tables based on creation date

--Delete all Tables based on creation date



 DECLARE @tname VARCHAR(100)
DECLARE @sql VARCHAR(max)

DECLARE db_cursor CURSOR FOR
SELECT name AS tname
FROM sys.objects
WHERE create_date < GETDATE() - 1-- Days old

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tname

WHILE @@FETCH_STATUS = 0
BEGIN
       SET @sql = 'DROP TABLE ' + @tname
       --EXEC (@sql)  -- For Executing
       PRINT @sql --For Printing

       FETCH NEXT FROM db_cursor INTO @tname
END

CLOSE db_cursor
DEALLOCATE db_cursor

Simplest way to Truncate/Delete all tables in a given database.

Simplest way to Truncate all tables in a given database.

Use databasename

EXEC sp_MSForEachTable 'Truncate TABLE ?' --- For Truncating all tables

Simplest way to Truncate all tables in a given database.

Use databasename

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"  --- For Deleting all tables

SQL Server SSRS: List out all Data Sources and all their dependency objects/Reports

 This Script will list all the data sources by their actual name, and all their dependent items:

SELECT C2.NAME AS Data_Source_Name
 ,C.NAME AS Dependent_Item_Name
 ,C.Path AS Dependent_Item_Path
FROM ReportServer.dbo.DataSource AS DS
INNER JOIN ReportServer.dbo.CATALOG AS C ON DS.ItemID = C.ItemID
 AND DS.Link IN (
  SELECT ItemID
  FROM ReportServer.dbo.CATALOG
  WHERE Type = 5
  ) --Type 5 identifies data sources
FULL JOIN ReportServer.dbo.CATALOG C2 ON DS.Link = C2.ItemID
WHERE C2.Type = 5
ORDER BY C2.NAME ASC
 ,C.NAME ASC;

GRANT ALL in SQL Server

A GRANT ALL syntax also exists, granting supposedly all the permissions
on a securable. But it is better not to use it, because it does not in fact
grant all permissions, only the ones defined in the SQL-92 ANSI standard.
More permissions are available for SQL Server objects than the permissions
defined in the ANSI standard. The GRANT ALL syntax is now deprecated.

• If the securable is a database, "ALL" means BACKUP DATABASE, BACKUP LOG, CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, and CREATE VIEW.
• If the securable is a scalar function, "ALL" means EXECUTE and REFERENCES.
• If the securable is a table-valued function, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
• If the securable is a stored procedure, "ALL" means EXECUTE.
• If the securable is a table, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
• If the securable is a view, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

Script to generate drop all tables statements in a database in SQL Server

SELECT 'Drop table ' + NAME
FROM sys.objects
WHERE schema_name(schema_id) = 'dbo'
 AND type = 'u'

Script to list out all recently ran queries in SQL Server

SELECT deqs.last_execution_time AS [Time]
 ,dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

Script to find backup size of all SQL Server databases backup size

USE msdb
GO

/* Please change device_type and backupset type combination as you need.
 
backupmediafamily device_type
 
2 = File
 
5 = Tape
 



backupset type
 
L = Log
 
I = Differential
 
D = Full
 
*/
SELECT (SUM(backup_size) + SUM(1536)) / 1024 / 1024 AS MBs
 ,(SUM(backup_size) + SUM(1536)) / 1024 / 1024 / 1024 AS GBs
FROM backupset
INNER JOIN (
 SELECT database_name
  ,MAX(backup_start_date) AS LastFullBackupDate
 FROM backupset
 WHERE media_set_id IN (
   SELECT media_set_id
   FROM backupmediafamily
   WHERE device_type = 2
   )
  AND type = 'D'
 GROUP BY database_name
 ) AS GetLastDate ON backupset.database_name = GetLastDate.database_name
 AND backupset.backup_start_date = GetLastDate.LastFullBackupDate

Script to list all SQL Server instance names in a server

SET NOCOUNT ON

DECLARE @CurrID INT
 ,@ExistValue INT
 ,@MaxID INT
 ,@SQL NVARCHAR(1000)
DECLARE @TCPPorts TABLE (
 PortType NVARCHAR(180)
 ,Port INT
 )
DECLARE @SQLInstances TABLE (
 InstanceID INT identity(1, 1) NOT NULL PRIMARY KEY
 ,InstName NVARCHAR(180)
 ,Folder NVARCHAR(50)
 ,StaticPort INT NULL
 ,DynamicPort INT NULL
 ,Platform INT NULL
 );
DECLARE @Plat TABLE (
 Id INT
 ,NAME VARCHAR(180)
 ,InternalValue VARCHAR(50)
 ,Charactervalue VARCHAR(50)
 )
DECLARE @Platform VARCHAR(100)

INSERT INTO @Plat
EXEC xp_msver platform

SELECT @Platform = (
  SELECT 1
  FROM @plat
  WHERE charactervalue LIKE '%86%'
  )

IF @Platform IS NULL
BEGIN
 INSERT INTO @SQLInstances (
  InstName
  ,Folder
  )
 EXEC xp_regenumvalues N'HKEY_LOCAL_MACHINE'
  ,N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';

 UPDATE @SQLInstances
 SET Platform = 64
END
ELSE
BEGIN
 INSERT INTO @SQLInstances (
  InstName
  ,Folder
  )
 EXEC xp_regenumvalues N'HKEY_LOCAL_MACHINE'
  ,N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';

 UPDATE @SQLInstances
 SET Platform = 32
END

DECLARE @Keyexist TABLE (Keyexist INT)

INSERT INTO @Keyexist
EXEC xp_regread 'HKEY_LOCAL_MACHINE'
 ,N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';

SELECT @ExistValue = Keyexist
FROM @Keyexist

IF @ExistValue = 1
 INSERT INTO @SQLInstances (
  InstName
  ,Folder
  )
 EXEC xp_regenumvalues N'HKEY_LOCAL_MACHINE'
  ,N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';

UPDATE @SQLInstances
SET Platform = 32
WHERE Platform IS NULL

SELECT @MaxID = MAX(InstanceID)
 ,@CurrID = 1
FROM @SQLInstances

WHILE @CurrID <= @MaxID
BEGIN
 DELETE
 FROM @TCPPorts

 SELECT @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
 
                              N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
 
                              N''TCPDynamicPorts'''
 FROM @SQLInstances
 WHERE InstanceID = @CurrID

 INSERT INTO @TCPPorts
 EXEC sp_executesql @SQL

 SELECT @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
 
                              N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
 
                              N''TCPPort'''
 FROM @SQLInstances
 WHERE InstanceID = @CurrID

 INSERT INTO @TCPPorts
 EXEC sp_executesql @SQL

 SELECT @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
 
                              N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
 
                              N''TCPDynamicPorts'''
 FROM @SQLInstances
 WHERE InstanceID = @CurrID

 INSERT INTO @TCPPorts
 EXEC sp_executesql @SQL

 SELECT @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
 
                              N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
 
                              N''TCPPort'''
 FROM @SQLInstances
 WHERE InstanceID = @CurrID

 INSERT INTO @TCPPorts
 EXEC sp_executesql @SQL

 UPDATE SI
 SET StaticPort = P.Port
  ,DynamicPort = DP.Port
 FROM @SQLInstances SI
 INNER JOIN @TCPPorts DP ON DP.PortType = 'TCPDynamicPorts'
 INNER JOIN @TCPPorts P ON P.PortType = 'TCPPort'
 WHERE InstanceID = @CurrID;

 SET @CurrID = @CurrID + 1
END

SELECT serverproperty('ComputerNamePhysicalNetBIOS') AS ServerName
 ,InstName
 ,StaticPort
 ,DynamicPort
 ,Platform
FROM @SQLInstances

SET NOCOUNT OFF

Script to list out all object level permissions in SQL Server

SELECT CASE 
  WHEN PERM.STATE <> 'W'
   THEN PERM.state_desc
  ELSE 'GRANT'
  END + SPACE(1) + PERM.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.NAME) + CASE 
  WHEN cl.column_id IS NULL
   THEN SPACE(0)
  ELSE '(' + QUOTENAME(cl.NAME) + ')'
  END + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default + CASE 
  WHEN PERM.STATE <> 'W'
   THEN SPACE(0)
  ELSE SPACE(1) + 'WITH GRANT OPTION'
  END AS '--Object Level Permissions'
FROM sys.database_permissions AS PERM
INNER JOIN sys.objects AS obj ON PERM.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl ON cl.column_id = PERM.minor_id
 AND cl.[object_id] = PERM.major_id
ORDER BY PERM.permission_name ASC
 ,PERM.state_desc ASC

SELECT CASE 
  WHEN PERM.STATE <> 'W'
   THEN PERM.state_desc
  ELSE 'GRANT'
  END + SPACE(1) + PERM.permission_name + SPACE(1) + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default + CASE 
  WHEN PERM.STATE <> 'W'
   THEN SPACE(0)
  ELSE SPACE(1) + 'WITH GRANT OPTION'
  END AS '--Database Level Permissions'
FROM sys.database_permissions AS PERM
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
WHERE PERM.major_id = 0
ORDER BY PERM.permission_name ASC
 ,PERM.state_desc ASC