你的位置:首页 > 数据库

[数据库]sql:SQL Server metadata queries


http://www.mssqltips.com/sqlservertip/3449/making-sql-server-metadata-queries-easier-with-these-new-views

http://www.sql-server-helper.com/functions/metadata-functions/index.aspx

http://sqlmag.com/t-sql/partitioned-indexes-and-querying-metadata

--查询表结构,函数,触发器,存储过程,视图的结构,主键,外键等关系--https://msdn.microsoft.com/en-us/library/ms187812.aspxselect * from LibrarySystem.information_schema.tablesselect TABLE_CATALOG, TABLE_SCHEMA,	TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from LibrarySystem.information_schema.columns where table_name = 'StaffList' select * from LibrarySystem.information_schema.columns where table_name = 'StaffList'select * from information_schema.columnsselect * from information_schema.CHECK_CONSTRAINTSselect * from information_schema.COLUMN_DOMAIN_USAGEselect * from information_schema.COLUMN_PRIVILEGESselect * from information_schema.CONSTRAINT_COLUMN_USAGEselect * from information_schema.CONSTRAINT_TABLE_USAGEselect * from information_schema.DOMAIN_CONSTRAINTSselect * from information_schema.DOMAINSselect * from information_schema.KEY_COLUMN_USAGEselect * from information_schema.PARAMETERSselect * from information_schema.REFERENTIAL_CONSTRAINTSselect * from information_schema.ROUTINESselect * from information_schema.ROUTINE_COLUMNSselect * from information_schema.SCHEMATAselect * from information_schema.TABLE_CONSTRAINTSselect * from information_schema.TABLE_PRIVILEGESselect * from information_schema.VIEW_COLUMN_USAGEselect * from information_schema.TABLESselect * from information_schema.VIEW_COLUMN_USAGEselect * from information_schema.VIEW_TABLE_USAGEselect * from information_schema.VIEWS--Metadata queriesSELECT SCHEMA_NAME();GO--SQL SERVER 2005 metadata queries--For User Tablesselect * from sys.objectswhere Type = 'U'--For Viewsselect * from sys.objectswhere Type = 'V'--For Stored Proceduresselect * from sys.objectswhere Type = 'P'--For Triggersselect * from sys.objectswhere Type = 'TR'--For Functionsselect * from sys.objectswhere Type = 'FN'--select * from sys.databasesselect * from sys.tablesselect * from sys.viewsselect * from sys.triggersselect * from sys.schemasselect * from sys.procedures--for SQL 2000:SELECT * -- User tablesFROM sysobjectsWHERE objectproperty( id, N'IsMSShipped' ) = 0 AND objectproperty( id, N'IsTable' ) = 1SELECT * -- Stored ProcsFROM sysobjectsWHERE objectproperty( id, N'IsMSShipped' ) = 0 AND objectproperty( id, N'IsProcedure' ) = 1SELECT * -- FunctionsFROM sysobjectsWHERE objectproperty( id, N'IsMSShipped' ) = 0 AND ( objectproperty( id, N'IsTableFunction' ) = 1   OR objectproperty( id, N'IsScalarFunction' ) = 1 )SELECT * -- ViewsFROM sysobjectsWHERE objectproperty( id, N'IsMSShipped' ) = 0 AND objectproperty( id, N'IsView' ) = 1--In SQL Server 2005 SELECT * -- User tablesFROM sys.objectsWHERE objectproperty( object_id, N'IsMSShipped' ) = 0 AND objectproperty( object_id, N'IsTable' ) = 1SELECT * -- Stored ProcsFROM sys.objectsWHERE objectproperty( object_id, N'IsMSShipped' ) = 0 AND objectproperty( object_id, N'IsProcedure' ) = 1SELECT * -- FunctionsFROM sys.objectsWHERE objectproperty( object_id, N'IsMSShipped' ) = 0 AND ( objectproperty( object_id, N'IsTableFunction' ) = 1   OR objectproperty( object_id, N'IsScalarFunction' ) = 1 )SELECT * -- ViewsFROM sys.objectsWHERE objectproperty( object_id, N'IsMSShipped' ) = 0 AND objectproperty( object_id, N'IsView' ) = 1-- SELECT   [object_id],  inbound_count = COUNT(CASE t WHEN 'I' THEN 1 END),  outbound_count = COUNT(CASE t WHEN 'O' THEN 1 END) FROM (  SELECT [object_id] = referenced_object_id, t = 'I'  FROM sys.foreign_keys  UNION ALL  SELECT [object_id] = parent_object_id, t = 'O'  FROM sys.foreign_keys ) AS c GROUP BY [object_id];

  

--http://www.mssqltips.com/sqlservertip/3449/making-sql-server-metadata-queries-easier-with-these-new-views--http://www.sqlteam.com/article/using-metadataCREATE VIEW metadata.tablesAS SELECT   t.[object_id],   [schema] = QUOTENAME(s.name),  [table] = QUOTENAME(t.name),  [object] = QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) FROM sys.tables AS t INNER JOIN sys.schemas AS s  ON t.[schema_id] = s.[schema_id];--Number of rows and size of the SQL Server tableCREATE FUNCTION metadata.tvf_spaceused( @object_id INT)RETURNS TABLEAS RETURN  (  SELECT   [rows],   reserved_kb = r,   data_kb = p,   index_size_kb = CASE WHEN u > p THEN u - p ELSE 0 END,   unused_kb = CASE WHEN r > u THEN r - u ELSE 0 END  FROM   (   SELECT     r = (SUM (p1.reserved_page_count) + COALESCE(MAX(it.r),0)) * 8,    u = (SUM (p1.used_page_count) + COALESCE(MAX(it.u),0)) * 8,    p = (SUM (CASE WHEN p1.index_id >= 2 THEN 0 ELSE     (p1.in_row_data_page_count + p1.lob_used_page_count + p1.row_overflow_used_page_count)    END) * 8),    [rows] = SUM (CASE WHEN p1.index_id IN (0,1)      THEN p1.row_count ELSE 0 END)   FROM sys.dm_db_partition_stats AS p1   LEFT OUTER JOIN   (    SELECT it.parent_id,     r = SUM(p2.reserved_page_count),     u = SUM(p2.used_page_count)    FROM sys.internal_tables AS it    INNER JOIN sys.dm_db_partition_stats AS p2    ON it.[object_id] = p2.[object_id]    WHERE it.parent_id = @object_id    AND it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236)    GROUP BY it.parent_id   ) AS it   ON p1.[object_id] = it.parent_id   WHERE p1.[object_id] = @object_id  ) AS x);GO--SELECT -- basic metadata t.[object_id],  t.[schema], t.[table], t.[object], -- mimic spaceused su.[rows],  su.reserved_kb, su.data_kb, su.index_size_kb, su.unused_kbFROM metadata.tables AS tCROSS APPLY metadata.tvf_spaceused(t.[object_id]) AS suORDER BY t.[object];--Last SQL Server table accessedCREATE VIEW metadata.table_accessAS SELECT   [object_id],  last_read = MAX(last_read),  last_write = MAX(last_write) FROM (  SELECT [object_id],    last_read = (SELECT MAX(d) FROM (VALUES    (last_user_seek),(last_user_scan),(last_user_lookup))     AS reads(d)),   last_write = (SELECT MAX(d) FROM (VALUES    (last_user_update))     AS writes(d))  FROM sys.dm_db_index_usage_stats ) AS x GROUP BY [object_id];--All kinds of data about SQL Server columnsCREATE VIEW metadata.columnsAS SELECT   c.[object_id],  column_count = COUNT(c.column_id),  column_list = STUFF((SELECT N',' + QUOTENAME(name)   FROM sys.columns AS c2   WHERE c2.[object_id] = c.[object_id]   ORDER BY c2.column_id   FOR