As dba and SQL Server performance optimization consultant at Ambient Consulting, I often come across the need to analyze performance bottlenecks on SQL Server instances that I see for the first time in my life. This can be a daunting task. As a rule, most companies do not have documentation on their databases. And if there is, then it is outdated, or its search takes several days.

In this article, I will share a basic set of scripts, digging up information about metadata using system functions, stored procedures, tables, dmv. Together, they reveal all the secrets of databases on the right instance - their size, file location, their design, including columns, data types, defaults, keys, and indexes.

If you have ever tried to get some of this information using the GUI, I think you will be pleasantly surprised by the amount of information that is obtained instantaneously using these scripts.
')
As with any scripts, first test them in a test environment before running in production. I would recommend you to drive them on MS test bases, such as AdventureWorks or pubs .

Well, enough words, let me show the scripts!

We study the server

Let's start with queries that provide information about your servers.

Basic information

First, some simple @@ Functions that will provide us with basic information.

--     Select @@SERVERNAME as [Server\Instance]; --  SQL Server Select @@VERSION as SQLServerVersion; --  SQL Server Select @@ServiceName AS ServiceInstance; --   (,     ) Select DB_NAME() AS CurrentDB_Name; 

How long does your SQL Server last after the last restart? Remember that the tempdb system database is re-created each time SQL Server restarts. Here is one of the methods for determining the last server restart time.

 SELECT @@Servername AS ServerName , create_date AS ServerStarted , DATEDIFF(s, create_date, GETDATE()) / 86400.0 AS DaysRunning , DATEDIFF(s, create_date, GETDATE()) AS SecondsRunnig FROM sys.databases WHERE name = 'tempdb'; GO 

Linked servers

Linked servers are connections that allow SQL Server to access other servers with data. Distributed requests can be run on different linked servers. It is useful to know whether your database server is isolated from others, or if it is connected to other servers.

 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 

List of all databases

First, we get a list of all databases on the server. Remember that on any server there are four or five system databases (master, model, msdb, tempdb and distribution, if you use replication). You will probably want to exclude these bases in the following queries. It is very easy to see the list of databases in SSMS, but these requests will be our “building blocks” for more complex requests.

There are several ways to get a list of all databases in T-SQL and below you will see some of them. Each method returns a similar result, but with some differences.

 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 

Last backup?

Stop! Before moving on, every good dba should find out if he has a fresh backup.

 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; 

It would be better if you immediately find out the path to the file with the latest backup.

 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 

Active user connections

It would be good to understand which databases are currently being used, especially if you are going to deal with performance problems.

Translator's note : this will only work in SQL Server 2012 and higher, in previous editions, the dmv sys.dm_exec_sessions was missing the database_id column. To find out which databases the users are currently working on, you can use sp_who.

 --  ,      sp_who 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; 

We study the database

Let's take a deeper look and see how we can gather information about objects in all of your databases using various directory and dmv views. Most of the queries presented in this section look inside only one database, so do not forget to select the desired database in SSMS or using the use database command. Also remember that you can always look in the context of which database the query will be executed with select db_name ().

The sys.objects system table is one of the keys to gathering information about the objects that make up your data model.

 --   U -  --     type  WHERE USE MyDatabase; GO SELECT * FROM sys.objects WHERE type = 'U'; 

Below is a list of object types for which we can get information (see the documentation on sys.objects on MSDN )


Other directory views, such as sys.tables and sys.views, refer to sys.objects and provide information about a particular type of object. With these views, plus the OBJECTPROPERTY function, we can get a huge amount of information on each of the objects that make up our database schema.

Location of database files

The physical location of the selected database, including the main data file (mdf), and the transaction log file (ldf), can be obtained using these queries.

 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 

Tables

Of course, the Object Explorer in SSMS shows the complete list of tables in the selected database, but it is more difficult to get some of the information using the GUI than with the help of scripts. The ANSI standard assumes reference to INFORMATION_SCHEMA views, but they do not provide information about objects that are not part of the standard (such as triggers, extended procedures, etc.), so it’s best to use SQL Server catalog views.

 EXEC sp_tables; -- ,      ,   --OR SELECT @@Servername AS ServerName , TABLE_CATALOG , TABLE_SCHEMA , TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME ; --OR SELECT @@Servername AS ServerName , DB_NAME() AS DBName , o.name AS 'TableName' , o.[Type] , o.create_date FROM sys.objects o WHERE o.Type = 'U' -- User table ORDER BY o.name; --OR SELECT @@Servername AS ServerName , DB_NAME() AS DBName , t.Name AS TableName, t.[Type], t.create_date FROM sys.tables t ORDER BY t.Name; GO 

Number of records in the table

If you do not know anything about the table, then all tables are equally important. The more you learn about tables, the more you divide them into conditionally more important and conditionally less important. In general, tables with a huge number of records often have a serious impact on performance.

In SSMS, we can right-click on any table, open properties on the Storage tab and see the number of records in the table.



It is quite hard to manually collect this information about all the tables. Again, if we write SELECT COUNT (*) FROM TABLENAME for each table, we will have to type a lot.

It is much more convenient to use T-SQL to generate a script. The script below will generate a set of T-SQL statements to get the number of rows in each table in the current database. Just run it, copy the result into a new window and run.

 SELECT 'Select ''' + DB_NAME() + '.' + SCHEMA_NAME(SCHEMA_ID) + '.' + LEFT(o.name, 128) + ''' as DBName, count(*) as Count From ' + o.name + ';' AS ' Script generator to get counts for all tables' FROM sys.objects o WHERE o.[type] = 'U' ORDER BY o.name; GO 

Translator's note : my request did not work, I added a schema to the table name.

 SELECT 'Select ''' + DB_NAME() + '.' + SCHEMA_NAME(SCHEMA_ID) + '.' + LEFT(o.name, 128) + ''' as DBName, count(*) as Count From ' + SCHEMA_NAME(SCHEMA_ID) + '.' + o.name + ';' AS ' Script generator to get counts for all tables' FROM sys.objects o WHERE o.[type] = 'U' ORDER BY o.name; 

sp_msForEachTable

Sp_msforeachtable is an undocumented function that “passes” across all tables in the database and executes the query, substituting for the? the name of the current table. There is also a similar sp_msforeachdb function that works at the database level.

There are several problems with this undocumented function, for example, the use of special characters in the names of objects. Those. if the name of the table or database contains a '-' character, the stored procedure, which is listed below, will fail.

 CREATE TABLE #rowcount ( Tablename VARCHAR(128) , Rowcnt INT ); EXEC sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?' SELECT * FROM #rowcount ORDER BY Tablename , Rowcnt; DROP TABLE #rowcount; 

The fastest way to get the number of records is a clustered index

All previous methods used COUNT (*), which slowly works out if there are more than 500K records in the table.

The fastest way to get the number of records in a table is to get the number of records in a clustered index or heap. Remember that although this method is the fastest, MS says that the information on the number of index entries and the actual number of rows in the table may not be the same, due to the fact that it takes a little time, but updating the information. In most cases, these values ​​are either the same or very, very close and will soon become the same.

 --       -- Hint:   ,    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; -- OR --     ,    DMV dm_db_partition_stats SELECT @@ServerName AS ServerName , DB_NAME() AS DBName , OBJECT_SCHEMA_NAME(ddps.object_id) AS SchemaName , OBJECT_NAME(ddps.object_id) AS TableName , i.Type_Desc , i.Name AS IndexUsedForCounts , SUM(ddps.row_count) AS Rows FROM sys.dm_db_partition_stats ddps JOIN sys.indexes i ON i.object_id = ddps.object_id AND i.index_id = ddps.index_id WHERE i.type_desc IN ( 'CLUSTERED', 'HEAP' ) -- This is key (1 index per table) AND OBJECT_SCHEMA_NAME(ddps.object_id) <> 'sys' GROUP BY ddps.object_id , i.type_desc , i.Name ORDER BY SchemaName , TableName; GO 

Search heaps (tables without clustered indexes)

Working with heaps is like working with a flat file, instead of a database. If you want to be guaranteed to receive a full table scan for any query, use heaps. I usually recommend adding the primary key to all heap tables.

 --  ( 1) SELECT @@Servername AS ServerName , DB_NAME() AS DBName , t.Name AS HeapTable , t.Create_Date FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id = i.object_id AND i.type_desc = 'HEAP' ORDER BY t.Name --OR --  ( 2) SELECT @@Servername AS ServerName , DB_NAME() AS DBName , t.Name AS HeapTable , t.Create_Date FROM sys.tables t WHERE OBJECTPROPERTY(OBJECT_ID, 'TableHasClustIndex') = 0 ORDER BY t.Name; --OR --  ( 3) +   SELECT @@ServerName AS Server , DB_NAME() AS DBName , OBJECT_SCHEMA_NAME(ddps.object_id) AS SchemaName , OBJECT_NAME(ddps.object_id) AS TableName , i.Type_Desc , SUM(ddps.row_count) AS Rows FROM sys.dm_db_partition_stats AS ddps JOIN sys.indexes i ON i.object_id = ddps.object_id AND i.index_id = ddps.index_id WHERE i.type_desc = 'HEAP' AND OBJECT_SCHEMA_NAME(ddps.object_id) <> 'sys' GROUP BY ddps.object_id , i.type_desc ORDER BY TableName; 

We deal with the activity in the table

When working on performance optimization, it is very important to know which tables are actively read, and which are actively recorded. Earlier we learned how many records in our tables, now let's see how often they are written and read in them.

Remember that this information from the dmv is cleared every time SQL Server is restarted. The longer the server is running, the more reliable the statistics. I feel much more confident with statistics collected in 30 days than with statistics collected in a week.

 -- /  --   ,     --   ,      SQL Server 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 [Reads&Writes] , ( SELECT DATEDIFF(s, create_date, GETDATE()) / 86400.0 FROM master.sys.databases WHERE name = 'tempdb' ) AS SampleDays , ( SELECT DATEDIFF(s, create_date, GETDATE()) AS SecoundsRunnig FROM master.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 AND ddius.database_id = DB_ID() GROUP BY OBJECT_NAME(ddius.object_id) ORDER BY [Reads&Writes] DESC; GO 

A much more advanced version of this query is represented by a cursor that collects information on all the tables of all databases on the server. In general, I am not a fan of cursors due to their low performance, but navigating through different databases is an excellent use for them.

 --     --  ,     --  ,    SQL Server --           --  ,   tempdb 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; 

Translator's note : the cursor will not work if you have a database with a status other than ONLINE.

Representation

Representations are, relatively speaking, queries stored in the database. You can think of them as virtual tables. The data is not stored in the views, but in our queries we refer to them in the same way as the tables.

In SQL Server, in some cases, we can update data using a view. To get a read-only view, you can use SELECT DISTINCT when creating it. The data “through” the view can be changed only if each row of the view corresponds to only one row in the “base” table. Any representation that does not meet this criterion, i.e. built on multiple tables, or using groupings, aggregate functions and calculations, will be read only.

 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 -- 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 

Synonyms

Several times in my career I came across a situation where I could not understand to which table the query refers. Submit a simple SELECT * FROM Client query. I'm looking for a table called Client, but I can't find it. Well, I think I should have this view, looking for a view called Client and still can’t find it. Maybe I was wrong with the database? As a result, it turns out that Client is a synonym for customers and the table is, in fact, called Customer. The marketing department wanted to refer to this table as a Client, and because of this, a synonym was created. Fortunately, the use of synonyms is rare, but the proceedings can cause some difficulties if you are not ready for them.

 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 --     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

Stored procedures are a group of scripts that are compiled into a single execution plan. We can use directory views to determine which CPs are created, what actions they perform, and on which tables.

 --   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 --     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 

By adding a simple condition in WHERE, we can get information only about those stored procedures that, for example, perform INSERT operations.

 WHERE o.[type] = 'P' -- Stored Procedures AND sm.definition LIKE '%insert%' ORDER BY o.name … 

By slightly modifying the condition in WHERE, we can collect information about CPs that are updating, deleting, or accessing certain tables.

Functions

Functions are stored in SQL Server, take any parameters and perform certain actions or calculations, and then return the result.

 --  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 --     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 

Triggers

A trigger is something like a stored procedure that is executed in response to certain actions with the table to which this trigger belongs. For example, we can create INSERT, UPDATE and DELETE 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 --     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 restrictions

CHECK constraints are a good tool for implementing business logic in a database. For example, some fields must be positive, or negative, or the date in one column must be greater than the date in another.

 -- 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 

Delve into the data model

, , « » , . , , , , ( ) ..

, , - .

Columns

. , Excel, , . , , .

 SELECT @@Servername AS Server , DB_NAME() AS DBName , isc.Table_Name AS TableName , isc.Table_Schema AS SchemaName , Ordinal_Position AS Ord , 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; --      --         / SELECT @@Servername AS Server , DB_NAME() AS DBName , Column_Name , Data_Type , Numeric_Precision AS Prec , Numeric_Scale AS Scale , Character_Maximum_Length , COUNT(*) AS Count FROM information_schema.columns isc INNER JOIN information_schema.tables ist ON isc.table_name = ist.table_name WHERE Table_type = 'BASE TABLE' GROUP BY Column_Name , Data_Type , Numeric_Precision , Numeric_Scale , Character_Maximum_Length; --      SELECT @@Servername AS ServerName , DB_NAME() AS DBName , Data_Type , Numeric_Precision AS Prec , Numeric_Scale AS Scale , Character_Maximum_Length AS [Length] , COUNT(*) AS COUNT FROM information_schema.columns isc INNER JOIN information_schema.tables ist ON isc.table_name = ist.table_name WHERE Table_type = 'BASE TABLE' GROUP BY Data_Type , Numeric_Precision , Numeric_Scale , Character_Maximum_Length ORDER BY Data_Type , Numeric_Precision , Numeric_Scale , Character_Maximum_Length -- Large object data types or Binary Large Objects(BLOBs) -- ,            "online" SELECT @@Servername AS ServerName , DB_NAME() AS DBName , isc.Table_Name , Ordinal_Position AS Ord , Column_Name , Data_Type AS BLOB_Data_Type , Numeric_Precision AS Prec , Numeric_Scale AS Scale , Character_Maximum_Length AS [Length] FROM information_schema.columns isc INNER JOIN information_schema.tables ist ON isc.table_name = ist.table_name WHERE Table_type = 'BASE TABLE' AND ( Data_Type IN ( 'text', 'ntext', 'image', 'XML' ) OR ( Data_Type IN ( 'varchar', 'nvarchar', 'varbinary' ) AND Character_Maximum_Length = -1 ) ) -- varchar(max), nvarchar(max), varbinary(max) ORDER BY isc.Table_Name , Ordinal_position; 

– , , . , get_date(). , – system_user , .

 -- 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 

– , , , .

 --   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 

identity

IDENTITY . .

 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 

, – best practice. best practice , , , . , « » . .

?

.

 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 

?

, SQL Server , .

. . , , .

 --    DMV 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 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 

, , , . , , , . . , SQL Server table scan , «» .

 -- Foreign Keys missing indexes -- ,            --  ,      ,   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 

Dependencies

… , . «-» . – sp_msdependecies. – , . – CTE.

sp_msdependencies

Sp_msdependencies – , .

 EXEC sp_msdependencies '?' -- Displays Help sp_MSobject_dependencies name = NULL, type = NULL, flags = 0x01fd name: name or null (all objects of type) type: type number (see below) or null if both null, get all objects in database flags is a bitmask of the following values: 0x10000 = return multiple parent/child rows per object 0x20000 = descending return order 0x40000 = return children instead of parents 0x80000 = Include input object in output result set 0x100000 = return only firstlevel (immediate) parents/children 0x200000 = return only DRI dependencies power(2, object type number(s)) to return in results set: 0 (1 - 0x0001) - UDF 1 (2 - 0x0002) - system tables or MS-internal objects 2 (4 - 0x0004) - view 3 (8 - 0x0008) - user table 4 (16 - 0x0010) - procedure 5 (32 - 0x0020) - log 6 (64 - 0x0040) - default 7 (128 - 0x0080) - rule 8 (256 - 0x0100) - trigger 12 (1024 - 0x0400) - uddt shortcuts: 29 (0x011c) - trig, view, user table, procedure 448 (0x00c1) - rule, default, datatype 4606 (0x11fd) - all but systables/objects 4607 (0x11ff) – all 

, sp_msdependencies, : Type, ObjName, Owner(Schema), Sequence.

(Sequence) – 1 . Sequence – « » .

, . , « » — . Sequence – . Sequence . / (constraints).

 EXEC sp_msdependencies NULL --     EXEC sp_msdependencies NULL, 3 --    

SSMS, , «View Dependencies» «, TABLENAME»:



:

 -- sp_MSdependencies —    -- ,      EXEC sp_msdependencies N'Sales.Customer',null, 1315327 -- Change Table Name 

SSMS, , « TABLENAME», , :



sp_msdependencies.

 -- sp_MSdependencies -   -- ,      EXEC sp_MSdependencies N'Sales.Customer', NULL, 266751 -- Change Table Name 

, SSMS, .



, msdependencies, .

 -- ,      EXEC sp_MSdependencies N'Sales.Customer', null, 1053183 -- Change Table 

, , .

 CREATE TABLE #TempTable1 ( Type INT , ObjName VARCHAR(256) , Owner VARCHAR(25) , Sequence INT ); INSERT INTO #TempTable1 EXEC sp_MSdependencies NULL SELECT * FROM #TempTable1 WHERE Type = 8 --Tables ORDER BY Sequence , ObjName DROP TABLE #TempTable1; 

«-» – , .

 --Independent tables SELECT Name AS InDependentTables FROM sys.tables WHERE object_id NOT IN ( SELECT referenced_object_id FROM sys.foreign_key_columns ) -- Check for parents AND object_id NOT IN ( SELECT parent_object_id FROM sys.foreign_key_columns ) -- Check for Dependents ORDER BY Name -- Tables with dependencies. SELECT DISTINCT OBJECT_NAME(referenced_object_id) AS ParentTable , OBJECT_NAME(parent_object_id) AS DependentTable , OBJECT_NAME(constraint_object_id) AS ForeignKeyName FROM sys.foreign_key_columns ORDER BY ParentTable , DependentTable -- Top level of the pyramid tables. Tables with no parents. SELECT DISTINCT OBJECT_NAME(referenced_object_id) AS TablesWithNoParent FROM sys.foreign_key_columns WHERE referenced_object_id NOT IN ( SELECT parent_object_id FROM sys.foreign_key_columns ) ORDER BY 1 -- Bottom level of the pyramid tables. -- Tables with no dependents. (These are the leaves on a tree.) SELECT DISTINCT OBJECT_NAME(parent_object_id) AS TablesWithNoDependents FROM sys.foreign_key_columns WHERE parent_object_id NOT IN ( SELECT referenced_object_id FROM sys.foreign_key_columns ) ORDER BY 1 -- Tables with both parents and dependents. -- Tables in the middle of the hierarchy SELECT DISTINCT OBJECT_NAME(referenced_object_id) AS MiddleTables FROM sys.foreign_key_columns WHERE referenced_object_id IN ( SELECT parent_object_id FROM sys.foreign_key_columns ) AND parent_object_id NOT IN ( SELECT referenced_object_id FROM sys.foreign_key_columns ) ORDER BY 1; -- in rare cases, you might find a self-referencing dependent table. -- Recursive (self) referencing table dependencies. SELECT DISTINCT OBJECT_NAME(referenced_object_id) AS ParentTable , OBJECT_NAME(parent_object_id) AS ChildTable , OBJECT_NAME(constraint_object_id) AS ForeignKeyName FROM sys.foreign_key_columns WHERE referenced_object_id = parent_object_id ORDER BY 1 , 2; 

CTE

, – CTE.

 -- How to find the hierarchical dependencies -- Solve recursive queries using Common Table Expressions (CTE) WITH TableHierarchy ( ParentTable, DependentTable, Level ) AS ( -- Anchor member definition (First level group to start the process) SELECT DISTINCT CAST(NULL AS INT) AS ParentTable , e.referenced_object_id AS DependentTable , 0 AS Level FROM sys.foreign_key_columns AS e WHERE e.referenced_object_id NOT IN ( SELECT parent_object_id FROM sys.foreign_key_columns ) -- Add filter dependents of only one parent table -- AND Object_Name(e.referenced_object_id) = 'User' UNION ALL -- Recursive member definition (Find all the layers of dependents) SELECT --Distinct e.referenced_object_id AS ParentTable , e.parent_object_id AS DependentTable , Level + 1 FROM sys.foreign_key_columns AS e INNER JOIN TableHierarchy AS d ON ( e.referenced_object_id ) = d.DependentTable ) -- Statement that executes the CTE SELECT DISTINCT OBJECT_NAME(ParentTable) AS ParentTable , OBJECT_NAME(DependentTable) AS DependentTable , Level FROM TableHierarchy ORDER BY Level , ParentTable , DependentTable; 

Conclusion

, , , «-», .

: ( , ) SQL Server 2005 SP3 . , (, , ), , - , , , - .