MSSQL Server Information

If you have ever started a new job and we told to “Dig into the database and learn” these scripts likely have come to mind whether or not you knew/know them.

SELECT @@Servername AS [Server Name]
	--,@@ServiceName AS [Service Instance]
        ,Name AS [Database Name]
	,create_date AS [Service Started]
	,DATEDIFF(s, create_date, GETDATE()) / 86400.0 AS [Days Running]
	,DATEDIFF(s, create_date, GETDATE()) AS [Seconds Runnig]
	,@@VERSION AS [Server Version]
FROM sys.databases
WHERE NAME = 'tempdb';
GO

EXEC sp_helpserver;
--OR
EXEC sp_linkedservers;
--OR
SELECT  @@SERVERNAME AS Server ,
        Server_Id AS LinkedServerID ,
        name AS LinkedServer ,
        Product ,
        Provider ,
        Data_Source ,
        Modify_Date
FROM    sys.servers
ORDER BY name;
GO

EXEC sp_helpdb;

--OR
EXEC sp_Databases;

--OR
SELECT @@SERVERNAME AS SERVER
	,NAME AS DBName
	,recovery_model_Desc AS RecoveryModel
	,Compatibility_level AS CompatiblityLevel
	,create_date
	,state_desc
FROM sys.databases
ORDER BY NAME;

--OR
SELECT @@SERVERNAME AS SERVER
	,d.NAME AS DBName
	,create_date
	,compatibility_level
	,m.physical_name AS FileName
FROM sys.databases d
JOIN sys.master_files m ON d.database_id = m.database_id
WHERE m.[type] = 0 -- data files only
ORDER BY d.NAME;
GO

SELECT @@Servername AS ServerName
	,d.NAME AS DBName
	,MAX(b.backup_finish_date) AS LastBackupCompleted
FROM sys.databases d
LEFT OUTER JOIN msdb..backupset b ON b.database_name = d.NAME
	AND b.[type] = 'D'
GROUP BY d.NAME
ORDER BY d.NAME;

--OR
SELECT @@Servername AS ServerName
	,d.NAME AS DBName
	,b.Backup_finish_date
	,bmf.Physical_Device_name
FROM sys.databases d
INNER JOIN msdb..backupset b ON b.database_name = d.NAME
	AND b.[type] = 'D'
INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id
ORDER BY d.NAME
	,b.Backup_finish_date DESC;
GO

SELECT  @@Servername AS Server ,
        DB_NAME(database_id) AS DatabaseName ,
        COUNT(database_id) AS Connections ,
        Login_name AS LoginName ,
        MIN(Login_Time) AS Login_Time ,
        MIN(COALESCE(last_request_end_time, last_request_start_time))
                                                         AS Last_Batch
FROM    sys.dm_exec_sessions
WHERE   database_id > 0
        AND DB_NAME(database_id) NOT IN ( 'master', 'msdb' )
GROUP BY database_id ,
         login_name
ORDER BY DatabaseName;

EXEC sp_Helpfile;

--OR
SELECT @@Servername AS SERVER
	,DB_NAME() AS DB_Name
	,File_id
	,Type_desc
	,NAME
	,LEFT(Physical_Name, 1) AS Drive
	,Physical_Name
	,RIGHT(physical_name, 3) AS Ext
	,Size
	,Growth
FROM sys.database_files
ORDER BY File_id;
GO

SELECT @@ServerName AS SERVER
	,DB_NAME() AS DBName
	,OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName
	,OBJECT_NAME(p.object_id) AS TableName
	,i.Type_Desc
	,i.NAME AS IndexUsedForCounts
	,SUM(p.Rows) AS Rows
FROM sys.partitions p
JOIN sys.indexes i ON i.object_id = p.object_id
	AND i.index_id = p.index_id
WHERE i.type_desc IN (
		'CLUSTERED'
		,'HEAP'
		)
	-- This is key (1 index per table)
	AND OBJECT_SCHEMA_NAME(p.object_id) <> 'sys'
GROUP BY p.object_id
	,i.type_desc
	,i.NAME
ORDER BY SchemaName
	,TableName;

DECLARE DBNameCursor CURSOR
FOR
SELECT NAME
FROM sys.databases
WHERE NAME NOT IN (
		'master'
		,'model'
		,'msdb'
		,'tempdb'
		,'distribution'
		)
ORDER BY NAME;

DECLARE @DBName NVARCHAR(128)
DECLARE @cmd VARCHAR(4000)

IF OBJECT_ID(N'tempdb..TempResults') IS NOT NULL
BEGIN
	DROP TABLE tempdb..TempResults
END

CREATE TABLE tempdb..TempResults (
	ServerName NVARCHAR(128)
	,DBName NVARCHAR(128)
	,TableName NVARCHAR(128)
	,Reads INT
	,Writes INT
	,ReadsWrites INT
	,SampleDays DECIMAL(18, 8)
	,SampleSeconds INT
	)

OPEN DBNameCursor

FETCH NEXT
FROM DBNameCursor
INTO @DBName

WHILE @@fetch_status = 0
BEGIN
	----------------------------------------------------
	-- Print @DBName
	SELECT @cmd = 'Use ' + @DBName + '; '

	SELECT @cmd = @cmd + ' Insert Into tempdb..TempResults
SELECT @@ServerName AS ServerName,
DB_NAME() AS DBName,
object_name(ddius.object_id) AS TableName ,
SUM(ddius.user_seeks
+ ddius.user_scans
+ ddius.user_lookups) AS Reads,
SUM(ddius.user_updates) as Writes,
SUM(ddius.user_seeks
+ ddius.user_scans
+ ddius.user_lookups
+ ddius.user_updates) as ReadsWrites,
(SELECT datediff(s,create_date, GETDATE()) / 86400.0
FROM sys.databases WHERE name = ''tempdb'') AS SampleDays,
(SELECT datediff(s,create_date, GETDATE())
FROM sys.databases WHERE name = ''tempdb'') as SampleSeconds
FROM sys.dm_db_index_usage_stats ddius
INNER JOIN sys.indexes i
ON ddius.object_id = i.object_id
AND i.index_id = ddius.index_id
WHERE objectproperty(ddius.object_id,''IsUserTable'') = 1 --True
AND ddius.database_id = db_id()
GROUP BY object_name(ddius.object_id)
ORDER BY ReadsWrites DESC;'

	--PRINT @cmd
	EXECUTE (@cmd)

	-----------------------------------------------------
	FETCH NEXT
	FROM DBNameCursor
	INTO @DBName
END

CLOSE DBNameCursor

DEALLOCATE DBNameCursor

SELECT *
FROM tempdb..TempResults
ORDER BY DBName
	,TableName;
--DROP TABLE tempdb..TempResults;

SELECT @@Servername AS ServerName
	,DB_NAME() AS DBName
	,o.NAME AS ViewName
	,o.[Type]
	,o.create_date
FROM sys.objects o
WHERE o.[Type] = 'V' -- View
ORDER BY o.NAME

--OR
SELECT @@Servername AS ServerName
	,DB_NAME() AS DBName
	,NAME AS ViewName
	,create_date
FROM sys.VIEWS
ORDER BY NAME

--OR
SELECT @@Servername AS ServerName
	,TABLE_CATALOG
	,TABLE_SCHEMA
	,TABLE_NAME
	,TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
ORDER BY TABLE_NAME

--OR
-- View details (Show the CREATE VIEW Code)
SELECT @@Servername AS ServerName
	,DB_NAME() AS DB_Name
	,o.NAME AS 'ViewName'
	,o.Type
	,o.create_date
	,sm.[DEFINITION] AS 'View script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE o.Type = 'V' -- View
ORDER BY o.NAME;
GO

SELECT @@Servername AS ServerName
	,DB_NAME() AS DBName
	,o.NAME AS ViewName
	,o.Type
	,o.create_date
FROM sys.objects o
WHERE o.[Type] = 'SN' -- Synonym
ORDER BY o.NAME;

--OR
-- synonymn details
SELECT @@Servername AS ServerName
	,DB_NAME() AS DBName
	,s.NAME AS synonyms
	,s.create_date
	,s.base_object_name
FROM sys.synonyms s
ORDER BY s.NAME;
GO

-- Stored Procedures
SELECT @@Servername AS ServerName
	,DB_NAME() AS DBName
	,o.NAME AS StoredProcedureName
	,o.[Type]
	,o.create_date
FROM sys.objects o
WHERE o.[Type] = 'P' -- Stored Procedures
ORDER BY o.NAME

--OR
-- Stored Procedure details
SELECT @@Servername AS ServerName
	,DB_NAME() AS DB_Name
	,o.NAME AS 'ViewName'
	,o.[type]
	,o.Create_date
	,sm.[definition] AS 'Stored Procedure script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.object_id
WHERE o.[type] = 'P' -- Stored Procedures
	-- AND sm.[definition] LIKE '%insert%'
	-- AND sm.[definition] LIKE '%update%'
	-- AND sm.[definition] LIKE '%delete%'
	-- AND sm.[definition] LIKE '%tablename%'
ORDER BY o.NAME;
GO

-- Functions
SELECT @@Servername AS ServerName
	,DB_NAME() AS DB_Name
	,o.NAME AS 'Functions'
	,o.[Type]
	,o.create_date
FROM sys.objects o
WHERE o.Type = 'FN' -- Function
ORDER BY o.NAME;

--OR
-- Function details
SELECT @@Servername AS ServerName
	,DB_NAME() AS DB_Name
	,o.NAME AS 'FunctionName'
	,o.[type]
	,o.create_date
	,sm.[DEFINITION] AS 'Function script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE o.[Type] = 'FN' -- Function
ORDER BY o.NAME;
GO

-- Table Triggers
SELECT @@Servername AS ServerName
	,DB_NAME() AS DBName
	,parent.NAME AS TableName
	,o.NAME AS TriggerName
	,o.[Type]
	,o.create_date
FROM sys.objects o
INNER JOIN sys.objects parent ON o.parent_object_id = parent.object_id
WHERE o.Type = 'TR' -- Triggers
ORDER BY parent.NAME
	,o.NAME

--OR
SELECT @@Servername AS ServerName
	,DB_NAME() AS DB_Name
	,Parent_id
	,NAME AS TriggerName
	,create_date
FROM sys.triggers
WHERE parent_class = 1
ORDER BY NAME;

--OR
-- Trigger Details
SELECT @@Servername AS ServerName
	,DB_NAME() AS DB_Name
	,OBJECT_NAME(Parent_object_id) AS TableName
	,o.NAME AS 'TriggerName'
	,o.Type
	,o.create_date
	,sm.[DEFINITION] AS 'Trigger script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE o.Type = 'TR' -- Triggers
ORDER BY o.NAME;
GO

-- Check Constraints
SELECT @@Servername AS ServerName
	,DB_NAME() AS DBName
	,parent.NAME AS 'TableName'
	,o.NAME AS 'Constraints'
	,o.[Type]
	,o.create_date
FROM sys.objects o
INNER JOIN sys.objects parent ON o.parent_object_id = parent.object_id
WHERE o.Type = 'C' -- Check Constraints
ORDER BY parent.NAME
	,o.NAME

--OR
--CHECK constriant definitions
SELECT @@Servername AS ServerName
	,DB_NAME() AS DBName
	,OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName
	,OBJECT_NAME(parent_object_id) AS TableName
	,parent_column_id AS Column_NBR
	,NAME AS CheckConstraintName
	,type
	,type_desc
	,create_date
	,OBJECT_DEFINITION(object_id) AS CheckConstraintDefinition
FROM sys.Check_constraints
ORDER BY TableName
	,SchemaName
	,Column_NBR
GO

SELECT @@Servername AS SERVER
	,DB_NAME() AS DBName
	,isc.Table_Name AS TableName
	,isc.Table_Schema AS SchemaName
	,Ordinal_Position AS Ord
	,isc.Column_Name
	,Data_Type
	,Numeric_Precision AS Prec
	,Numeric_Scale AS Scale
	,Character_Maximum_Length AS [Len]
	,-- -1 means MAX like Varchar(MAX)
	Is_Nullable
	,Column_Default
	,Table_Type
FROM INFORMATION_SCHEMA.COLUMNS isc
INNER JOIN information_schema.tables ist ON isc.table_name = ist.table_name
-- WHERE Table_Type = 'BASE TABLE' -- 'Base Table' or 'View'
ORDER BY DBName
	,TableName
	,SchemaName
	,Ordinal_position;

-- Table Defaults
SELECT @@Servername AS ServerName
	,DB_NAME() AS DBName
	,parent.NAME AS TableName
	,o.NAME AS Defaults
	,o.[Type]
	,o.Create_date
FROM sys.objects o
INNER JOIN sys.objects parent ON o.parent_object_id = parent.object_id
WHERE o.[Type] = 'D' -- Defaults
ORDER BY parent.NAME
	,o.NAME

--OR
-- Column Defaults
SELECT @@Servername AS ServerName
	,DB_NAME() AS DB_Name
	,OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName
	,OBJECT_NAME(parent_object_id) AS TableName
	,parent_column_id AS Column_NBR
	,NAME AS DefaultName
	,[type]
	,type_desc
	,create_date
	,OBJECT_DEFINITION(object_id) AS Defaults
FROM sys.default_constraints
ORDER BY TableName
	,Column_NBR

--OR
-- Column Defaults
SELECT @@Servername AS ServerName
	,DB_NAME() AS DB_Name
	,OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName
	,t.NAME AS TableName
	,c.Column_ID AS Ord
	,c.NAME AS Column_Name
	,OBJECT_NAME(default_object_id) AS DefaultName
	,OBJECT_DEFINITION(default_object_id) AS Defaults
FROM sys.Tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE default_object_id <> 0
ORDER BY TableName
	,SchemaName
	,c.Column_ID
GO

-- Computed columns
SELECT @@Servername AS ServerName
	,DB_NAME() AS DBName
	,OBJECT_SCHEMA_NAME(object_id) AS SchemaName
	,OBJECT_NAME(object_id) AS Tablename
	,Column_id
	,NAME AS Computed_Column
	,[Definition]
	,is_persisted
FROM sys.computed_columns
ORDER BY SchemaName
	,Tablename
	,[Definition];

--Or
-- Computed Columns
SELECT @@Servername AS ServerName
	,DB_NAME() AS DBName
	,OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName
	,t.NAME AS TableName
	,c.Column_ID AS Ord
	,c.NAME AS Computed_Column
FROM sys.Tables t
INNER JOIN sys.Columns c ON t.object_id = c.object_id
WHERE is_computed = 1
ORDER BY t.NAME
	,SchemaName
	,c.Column_ID
GO

SELECT @@Servername AS ServerName
	,DB_NAME() AS DBName
	,OBJECT_SCHEMA_NAME(object_id) AS SchemaName
	,OBJECT_NAME(object_id) AS TableName
	,Column_id
	,NAME AS IdentityColumn
	,Seed_Value
	,Last_Value
FROM sys.identity_columns
ORDER BY SchemaName
	,TableName
	,Column_id;
GO

SELECT @@Servername AS ServerName
	,DB_NAME() AS DB_Name
	,o.NAME AS TableName
	,i.NAME AS IndexName
FROM sys.objects o
INNER JOIN sys.indexes i ON o.object_id = i.object_id
WHERE o.Type = 'U' -- User table
	AND LEFT(i.NAME, 1) <> '_' -- Remove hypothetical indexes
ORDER BY o.NAME
	,i.NAME;
GO

-- Missing Indexes DMV Suggestions
SELECT @@ServerName AS ServerName
	,DB_NAME() AS DBName
	,t.NAME AS 'Affected_table'
	,(
		LEN(ISNULL(ddmid.equality_columns, N'') + CASE
				WHEN ddmid.equality_columns IS NOT NULL
					AND ddmid.inequality_columns IS NOT NULL
					THEN ','
				ELSE ''
				END) - LEN(REPLACE(ISNULL(ddmid.equality_columns, N'') + CASE
					WHEN ddmid.equality_columns IS NOT NULL
						AND ddmid.inequality_columns IS NOT NULL
						THEN ','
					ELSE ''
					END, ',', ''))
		) + 1 AS K
	,COALESCE(ddmid.equality_columns, '') + CASE
		WHEN ddmid.equality_columns IS NOT NULL
			AND ddmid.inequality_columns IS NOT NULL
			THEN ','
		ELSE ''
		END + COALESCE(ddmid.inequality_columns, '') AS Keys
	,COALESCE(ddmid.included_columns, '') AS [include]
	,'Create NonClustered Index IX_' + t.NAME + '_missing_' + CAST(ddmid.index_handle AS VARCHAR(20)) + ' On ' + ddmid.[statement] COLLATE database_default + ' (' + ISNULL(ddmid.equality_columns, '') + CASE
		WHEN ddmid.equality_columns IS NOT NULL
			AND ddmid.inequality_columns IS NOT NULL
			THEN ','
		ELSE ''
		END + ISNULL(ddmid.inequality_columns, '') + ')' + ISNULL(' Include (' + ddmid.included_columns + ');', ';') AS sql_statement
	,ddmigs.user_seeks
	,ddmigs.user_scans
	,CAST((ddmigs.user_seeks + ddmigs.user_scans) * ddmigs.avg_user_impact AS BIGINT) AS 'est_impact'
	,avg_user_impact
	,ddmigs.last_user_seek
	,(
		SELECT DATEDIFF(Second, create_date, GETDATE()) Seconds
		FROM sys.databases
		WHERE NAME = 'tempdb'
		) SecondsUptime
-- Select *
FROM sys.dm_db_missing_index_groups ddmig
INNER JOIN sys.dm_db_missing_index_group_stats ddmigs ON ddmigs.group_handle = ddmig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details ddmid ON ddmig.index_handle = ddmid.index_handle
INNER JOIN sys.tables t ON ddmid.OBJECT_ID = t.OBJECT_ID
WHERE ddmid.database_id = DB_ID()
ORDER BY est_impact DESC;
GO

-- Foreign Keys
SELECT @@Servername AS ServerName
	,DB_NAME() AS DB_Name
	,parent.NAME AS 'TableName'
	,o.NAME AS 'ForeignKey'
	,o.[Type]
	,o.Create_date
FROM sys.objects o
INNER JOIN sys.objects parent ON o.parent_object_id = parent.object_id
WHERE o.[Type] = 'F' -- Foreign Keys
ORDER BY parent.NAME
	,o.NAME

--OR
SELECT f.NAME AS ForeignKey
	,SCHEMA_NAME(f.SCHEMA_ID) AS SchemaName
	,OBJECT_NAME(f.parent_object_id) AS TableName
	,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName
	,SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName
	,OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName
	,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
ORDER BY TableName
	,ReferenceTableName;
GO

-- Foreign Keys missing indexes
-- Note this script only works for creating single column indexes.
-- Multiple FK columns are out of scope for this script.
SELECT DB_NAME() AS DBName
	,rc.Constraint_Name AS FK_Constraint
	,
	-- rc.Constraint_Catalog AS FK_Database,
	-- rc.Constraint_Schema AS FKSch,
	ccu.Table_Name AS FK_Table
	,ccu.Column_Name AS FK_Column
	,ccu2.Table_Name AS ParentTable
	,ccu2.Column_Name AS ParentColumn
	,I.NAME AS IndexName
	,CASE
		WHEN I.NAME IS NULL
			THEN 'IF NOT EXISTS (SELECT * FROM sys.indexes
                                    WHERE object_id = OBJECT_ID(N''' + RC.Constraint_Schema + '.' + ccu.Table_Name + ''') AND name = N''IX_' + ccu.Table_Name + '_' + ccu.Column_Name + ''') ' + 'CREATE NONCLUSTERED INDEX IX_' + ccu.Table_Name + '_' + ccu.Column_Name + ' ON ' + rc.Constraint_Schema + '.' + ccu.Table_Name + '( ' + ccu.Column_Name + ' ASC ) WITH (PAD_INDEX = OFF,
                                   STATISTICS_NORECOMPUTE = OFF,
                                   SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF,
                                   DROP_EXISTING = OFF, ONLINE = ON);'
		ELSE ''
		END AS SQL
FROM information_schema.referential_constraints RC
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON rc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu2 ON rc.UNIQUE_CONSTRAINT_NAME = ccu2.CONSTRAINT_NAME
LEFT JOIN sys.columns c ON ccu.Column_Name = C.NAME
	AND ccu.Table_Name = OBJECT_NAME(C.OBJECT_ID)
LEFT JOIN sys.index_columns ic ON C.OBJECT_ID = IC.OBJECT_ID
	AND c.column_id = ic.column_id
	AND index_column_id = 1
-- index found has the foreign key
--  as the first column
LEFT JOIN sys.indexes i ON IC.OBJECT_ID = i.OBJECT_ID
	AND ic.index_Id = i.index_Id
WHERE I.NAME IS NULL
ORDER BY FK_table
	,ParentTable
	,ParentColumn;
GO

EXEC sp_msdependencies NULL    -- List all database dependencies
EXEC sp_msdependencies NULL, 3 -- List table dependencies

References & Credit:

• http://www.pinaldave.com/ / http://blog.sqlauthority.com/
• https://www.simple-talk.com/sql/database-administration/exploring-your-sql-server-databases-with-t-sql/