How to identify Primary and foreign key column information in SQL Server

Here is a small script to get information about the Primary Key column(s) and Foreign Key column(s) in SQL Server.

Script:


;WITH GETINFO AS
(
	SELECT	DBNAME = CONVERT(SYSNAME,DB_NAME()), 
				OWNERNAME = CONVERT(SYSNAME,SCHEMA_NAME(OBJ1.SCHEMA_ID)), 
				[PRIMARYTABLE NAME] = CONVERT(SYSNAME,OBJ1.NAME), 
				PKCOLUMN_NAME = CONVERT(SYSNAME,COL1.NAME), 
				[FOREIGNTABLE NAME]= CONVERT(SYSNAME,OBJ2.NAME), 
				FKCOLUMN_NAME = CONVERT(SYSNAME,COL2.NAME), 
				ORDINALPOSITION             = ISNULL(CONVERT(SMALLINT,FKCOL.CONSTRAINT_COLUMN_ID),0),
				[FOREIGNKEY NAME] = CONVERT(SYSNAME,OBJECT_NAME(FK.OBJECT_ID)), 
				[PRIMARYKEY NAME] = CONVERT(SYSNAME,I.NAME)
	FROM   SYS.ALL_OBJECTS OBJ1, SYS.ALL_OBJECTS OBJ2, SYS.ALL_COLUMNS COL1, SYS.ALL_COLUMNS COL2, SYS.FOREIGN_KEYS FK 
	INNER JOIN SYS.FOREIGN_KEY_COLUMNS FKCOL ON (FKCOL.CONSTRAINT_OBJECT_ID = FK.OBJECT_ID) 
	INNER JOIN SYS.INDEXES I  ON (FK.REFERENCED_OBJECT_ID = I.OBJECT_ID AND FK.KEY_INDEX_ID = I.INDEX_ID) 
	WHERE  OBJ1.OBJECT_ID = FK.REFERENCED_OBJECT_ID AND OBJ2.OBJECT_ID = FK.PARENT_OBJECT_ID 
				AND COL1.OBJECT_ID = FK.REFERENCED_OBJECT_ID AND COL2.OBJECT_ID = FK.PARENT_OBJECT_ID 
				AND COL1.COLUMN_ID = FKCOL.REFERENCED_COLUMN_ID AND COL2.COLUMN_ID = FKCOL.PARENT_COLUMN_ID
),SHOWINFO AS
(
	SELECT DISTINCT DBNAME,OWNERNAME,[PRIMARYTABLE NAME],[FOREIGNTABLE NAME],
           PRIMARYKEYCOLUMNNAMES=  REPLACE( 
			   (SELECT A.PKCOLUMN_NAME AS [data()]
				FROM GETINFO A
				WHERE A.[PRIMARYTABLE NAME] = B.[PRIMARYTABLE NAME] AND A.[FOREIGNKEY NAME] = B.[FOREIGNKEY NAME] AND A.[PRIMARYKEY NAME] = B.[PRIMARYKEY NAME]
				ORDER BY A.ORDINALPOSITION FOR XML PATH ('') ), ' ', ',') ,
           FOREIGNKEYCOLUMNNAMES=  REPLACE( 
			   (SELECT A.FKCOLUMN_NAME AS [data()]
                FROM GETINFO A
                WHERE A.[PRIMARYTABLE NAME] = B.[PRIMARYTABLE NAME] AND A.[FOREIGNKEY NAME] = B.[FOREIGNKEY NAME] AND A.[PRIMARYKEY NAME] = B.[PRIMARYKEY NAME]
                ORDER BY A.ORDINALPOSITION FOR XML PATH ('') ), ' ', ',') 
	FROM GETINFO B 
)SELECT * FROM SHOWINFO 

How to move TEMPDB files to different drives in SQL Server

Today, we are going to see how to move the TEMPDB files from one location to another.
First off, we should know the number of files and the its current location.
Query


SELECT NAME,PHYSICAL_NAME FROM SYS.MASTER_FILES WHERE DATABASE_ID=2

Query to move the files to new location

You may change the @NEWPATH variable value as per your new location.

DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @NEWPATH VARCHAR(MAX) = 'D:\DATA\DATA'
SET @SQL= @SQL + (SELECT 'ALTER DATABASE TEMPDB MODIFY FILE (NAME = ' + NAME + ', FILENAME = ''' + 
				@NEWPATH + RIGHT(PHYSICAL_NAME,CHARINDEX('\',REVERSE(PHYSICAL_NAME))) + ''');'
FROM SYS.MASTER_FILES WHERE DATABASE_ID = 2 FOR XML PATH(''))

EXEC ( @SQL )

Once you executed the above query, you need to restart the server to see the changes.

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

You can also go to the previous file location and delete the old files from the folder.

It is important to know that the moving of TEMPDB files no longer supporting using backup & restore OR  detaching & attaching(system databases).

T-SQL Script to restore backup files from a folder in SQL Server

Very often, myself or my friends were in need of a script to restore the backups from a predefined folder. It was hard to write the script in the hour of need. Hence, I thought I will share the script that has been created recently. Hope this script would be useful for you as well.


DECLARE @BACKUPFILENAME VARCHAR(MAX) = ''
DECLARE @MDFLOGICALNAME NVARCHAR(100)
DECLARE @LDFLOGICALNAME NVARCHAR(100)
DECLARE @DEST_PATH NVARCHAR(100)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DATABASENAME SYSNAME
CREATE TABLE #TRAILUPGRADEBACKUPFILES (ID INT IDENTITY(1,1),FILENAME SYSNAME,RESTORE_DRIVE CHAR(1))

/*********************************************************************************************************************************/
--The below code fetches the file list(backup file names) to be restored from the pre-defined folder
DECLARE @CommandShell TABLE ( Line VARCHAR(512)) 
DECLARE  @CMD VARCHAR(512) ,@BackupFilePath NVARCHAR(256)
SET @BackupFilePath = 'C:\TEMP_TOBEDELETED\' 
SET @CMD = 'DIR /B ' + @BackupFilePath +  ' /TC' 
				
INSERT INTO @CommandShell EXEC MASTER..xp_cmdshell   @CMD 
    
-- Delete lines not containing filename
DELETE 
FROM   @CommandShell 
WHERE  Line is null

If((Select Top 1 Line From @CommandShell) = 'Access is denied.') 
    PRINT 'Folder access is not provided for network path'
				
INSERT INTO #TRAILUPGRADEBACKUPFILES (FILENAME)
	Select * From @CommandShell A WHERE CHARINDEX('.BAK',LINE) > 0
/*********************************************************************************************************************************/

CREATE TABLE #TFILELISTONLY 
(
    TLOGINNAME SYSNAME,TPHYSICALNAME VARCHAR(MAX),
    TTYPE VARCHAR(1),TFILEGROUPNAME  VARCHAR(MAX),
    TSIZE BIGINT,TMAXSIZE BIGINT,TFIELD  VARCHAR(MAX),
    TCREATELSN VARCHAR(MAX),TDROPLSN VARCHAR(MAX),
    TUNIQUEID VARCHAR(MAX),READONLYLSN VARCHAR(MAX),
    READWRITELSN VARCHAR(MAX),BACKUPSIZEINBYTES VARCHAR(MAX),
    SOURCEBLOCKSIZE VARCHAR(MAX),FILEGROUPID VARCHAR(MAX),
    LOGGROUPGUID VARCHAR(MAX),DIFFERENTIALLSN VARCHAR(MAX),
    DIFFERENTIALBASEGUID VARCHAR(MAX),ISREADONLY VARCHAR(MAX),
    ISPRESENT VARCHAR(MAX),TDEHUMBPRINT VARCHAR(MAX),
)

WHILE EXISTS(SELECT 1 FROM #TRAILUPGRADEBACKUPFILES)
BEGIN
	
	SET @BACKUPFILENAME = (SELECT TOP 1 FILENAME FROM #TRAILUPGRADEBACKUPFILES ORDER BY FILENAME ASC)
	--Restore Path needs to be provided in the below
	SET @DEST_PATH = 'C:\DATA\'
		
	SET @DATABASENAME = (SUBSTRING(@BACKUPFILENAME,0,CHARINDEX('.',@BACKUPFILENAME)))
				
	--To check if the database is already present in the environment, If present, Drop the database
	IF Exists(SELECT 1 FROM master.sys.databases WHERE name = @DATABASENAME)
	BEGIN
	
		EXEC('DROP Database '+@DATABASENAME)
		Print @BACKUPFILENAME + ' has been successfully dropped.'
		
	END

	INSERT INTO #TFILELISTONLY
		EXEC ('RESTORE FILELISTONLY FROM DISK ='''+ @BackupFilePath +  @BACKUPFILENAME+'''')

	SET @MDFLOGICALNAME = (SELECT TLOGINNAME FROM #TFILELISTONLY WHERE TTYPE ='D')
	SELECT @LDFLOGICALNAME = (SELECT TLOGINNAME FROM #TFILELISTONLY WHERE TTYPE ='L')
        
	SELECT @SQL ='RESTORE DATABASE ' + @DATABASENAME + ' FROM DISK = ''' +  @BackupFilePath + @BACKUPFILENAME + ''' 
		WITH MOVE ''' + @MDFLOGICALNAME + ''' TO ''' + @DEST_PATH + @DATABASENAME + '.MDF'', 
		MOVE '''  + +  @LDFLOGICALNAME + ''' TO ''' + @DEST_PATH + @DATABASENAME + '.LDF'''
	
	EXEC (@SQL)
	
	Print @BACKUPFILENAME + ' has been successfully restored.'
		
	EXEC ('ALTER DATABASE [' + @DATABASENAME + '] SET RECOVERY SIMPLE;')

	DELETE FROM #TFILELISTONLY


	DELETE FROM #TRAILUPGRADEBACKUPFILES WHERE FILENAME =@BACKUPFILENAME	

END

DROP TABLE #TFILELISTONLY,#TRAILUPGRADEBACKUPFILES

How to identify start up Parameters in SQL Server using T-SQL

Here is a very simple script to identify the startup parameter in SQL Server using T-SQL.


SELECT
    registry_key,
    value_name,
    value_data
FROM sys.dm_server_registry 
WHERE 
    registry_key LIKE N'%MSSQLServer\Parameters';

If you are aware of any other methods, I request you to share for other’s benefits.

EDIT: (On 22nd Aug 2016)
One of my colleague executed the above script and reported me back that he is getting the below error:

Msg 208, Level 16, State 1, Line 9
Invalid object name 'sys.dm_server_registry'.

The issue was that the version of his SQL Server was SQL Server 2008R2 (RTM).
This is a new DMV introduced from service pack (SP1) and available only for later versions of SQL Server 2008R2(SP1).

How to identify Obsolete/unused Indexes in SQL Server

What is an obsolete Index?

Obsolete or Unused Index is an index without any seeks/scans or lookups (applicable only for Clustered).So if the sum of all seeks+scans+lookups is zero, that is an indication the index is not being used for any read access. These indexes will be an overhead in your application as those needs to be updated and maintained unnecessarily for any change in the key columns values.

Here is a very simple script to identify obsolete indexes in your environment.

Note: The script validates the below points:

1. Indexes with 0 read access (seek+scan+lookups)
2. Only applicable to non clustered indexes
3. Those indexes should not be part of PRIMARY or UNIQUE constraints


;WITH INDEXUSAGESTATS AS
(
	SELECT  
			I.OBJECT_ID,  OBJECT_NAME(I.OBJECT_ID) AS TABLE_NAME,  I.INDEX_ID,
			SUM(I.USER_SEEKS) AS SEEKS,  SUM(I.USER_SCANS) AS SCANS,  SUM(I.USER_LOOKUPS) AS LOOKUPS, SUM(I.USER_UPDATES) AS WRITES
	FROM SYS.TABLES T
	INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS I  ON T.OBJECT_ID = I.OBJECT_ID
	GROUP BY
			I.OBJECT_ID, I.INDEX_ID
 )
SELECT
		'DROP INDEX ' + QUOTENAME(IUS.TABLE_NAME) + '.' + QUOTENAME(I.NAME) AS DROPINDEXSTATEMENT,
		IUS.TABLE_NAME AS TABLENAME, I.NAME AS INDEXNAME, I.TYPE_DESC AS INDEXTYPE,
		IUS.SEEKS AS SEEKS, IUS.SCANS AS SCANS, IUS.LOOKUPS AS LOOKUPS, IUS.WRITES  AS TOTALWRITES,
		IUS.SEEKS + IUS.SCANS + IUS.LOOKUPS AS TOTALACCESSES
FROM INDEXUSAGESTATS AS IUS
INNER JOIN SYS.INDEXES I 	ON IUS.OBJECT_ID = I.OBJECT_ID  AND IUS.INDEX_ID = I.INDEX_ID
INNER JOIN SYS.TABLES B ON I.OBJECT_ID = B.OBJECT_ID
WHERE 
		/*OBSOLETE INDEX IS THOSE WITHOUT ANY SEEKS, SCANS and LOOKUPS*/
		IUS.SEEKS + IUS.SCANS + IUS.LOOKUPS = 0 
		/*QUERY EVALUATES ONLY NONCLUSTERED INDEXES THAT CAN NIETHER BE PRIMARY OR UNIQUE KEY*/
		AND I.TYPE_DESC = 'NONCLUSTERED'  AND I.IS_PRIMARY_KEY = 0  AND I.IS_UNIQUE = 0 
ORDER BY IUS.TABLE_NAME, I.NAME