The magical of SQL Server data dictionary

Here are some of the requirements that you might say with the SQL Server Management Studio client to see if you can, why bother! Yes, we can manually check when 1 tables, but when there are 10 tables, 100 tables? Manual inspection is not only time-consuming, but also very error-prone, and is fast and efficient through data dictionary queries.

• Find out what columns the primary key of a table contains?

--You can modify the conditions in the WHERE clause, as needed

 select  *   From  information_schema. Key_column_usage where  objectproperty  ( Span style= "COLOR: #ff00ff" >object_id  (constraint_name),  " isprimarykey  " ) =  Span style= "font-weight:bold; Color: #800000 ">1  and  table_schema=   " dbo   '  

• Count the number of tables in a schema

--can modify the conditions of the WHERE clause as needed, for example, the table name satisfies certain pattern:table_name like ' account% '

SELECT *  from WHERE Table_schema='DBO'

• To count all tables that contain a field name

SELECT  from WHERE column_name=' <COLUMN_NAME> '

• Number of tables with a larger amount of statistical data

 select  t.name, s.row_count from  Sys.tables t join   Sys.dm_db_partition_stats s  on  t.object_id  =  s.object_id  and  t.type_ Desc =   "  user_table   " and  s.index_id =  1  and  t.name =   " <TABLE_NAME>   " 

• Find database objects that depend on a table (above version 2008)

-When we want to discard a table, we can use this method to find all the affected objects.

SELECT         referencing_schema_name, Referencing_entity_name,         Referencing_class_desc, Is_caller_dependent  from Sys.dm_sql_referencing_entities ('<TableName> ' OBJECT')

• Find database objects that a stored procedure depends on (version 2008 or higher)

SELECT         referenced_schema_name, Referenced_entity_name, Referenced_minor_name,         Referenced_class_desc, Is_ Caller_dependent, is_ambiguous from sys.dm_sql_referenced_entities ('< storedprocedurename>'OBJECT');

The magical of SQL Server data dictionary