sql server 数据字典的妙用

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), ‘ISPRIMARYKEY‘) = 1 AND TABLE_SCHEMA=‘dbo‘ AND TABLE_NAME=‘EXPRESSION‘

• 统计某个schema下有多少个表

--可以根据需要，修改where字句的条件，例如 表名满足一定的pattern :table_name like ‘ACCOUNT%’

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=‘DBO‘

• 统计包含某个字段名的所有表

SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME=‘<COLUMN_NAME>‘

• 统计数据量比较大的表的条数

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

• 查找依赖于某个表的数据库对象（2008版本以上）

--当我们要把某张表废弃掉，用这个方法可以找出所有受影响的对象

SELECT
        referencing_schema_name, referencing_entity_name, 
        referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities (‘<TableName>‘, ‘OBJECT‘)

• 查找某个存储过程依赖的数据库对象（2008版本以上）

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‘);

sql server 数据字典的妙用

标签：