MSSQL Server Information

Posted on August 27, 2015May 13, 2016 By David Kittell

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:

Originally Posted on August 27, 2015
Last Updated on May 13, 2016

All information on this site is shared with the intention to help. Before any source code or program is ran on a production (non-development) system it is suggested you test it and fully understand what it is doing not just what it appears it is doing. I accept no responsibility for any damage you may do with this code.

Related

Code MSSQL MSSQL - Audit SQL