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 xml PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,1,N''),    has_identity_column = COUNT(NULLIF(c.is_identity,0)),    computed_column_count = COUNT(NULLIF(c.is_computed,0)),    persisted_computed_column_count = COUNT(NULLIF(cc.is_persisted,0)),    LOB_column_count = COUNT    (      CASE         WHEN c.system_type_id IN (34,35,99,241) THEN 1        WHEN c.system_type_id IN (165,167,231) AND c.max_length = -1 THEN 1      END    ),    XML_column_count = COUNT(CASE WHEN c.system_type_id = 241 THEN 1 END),    spatial_column_count = COUNT(CASE WHEN c.user_type_id IN (129,130) THEN 1 END),    hierarchyid_column_count = COUNT(CASE WHEN c.user_type_id = 128 THEN 1 END),    rowversion_column_count = COUNT(CASE WHEN c.system_type_id = 189 THEN 1 END),    GUID_column_count = COUNT(CASE WHEN c.system_type_id = 36 THEN 1 END),    deprecated_column_count = COUNT(CASE WHEN c.system_type_id IN (34,35,99) THEN 1 END),    alias_type_count = COUNT(NULLIF(t.is_user_defined,0))  FROM sys.columns AS c  INNER JOIN sys.types AS t  ON c.system_type_id = t.system_type_id  LEFT OUTER JOIN sys.computed_columns AS cc  ON c.[object_id] = cc.[object_id]  AND c.column_id = cc.column_id  GROUP BY c.[object_id];--All kinds of data about SQL Server indexesCREATE VIEW metadata.indexesAS  SELECT     i.[object_id],    i.has_clustered_index,    i.has_primary_key,    i.unique_index_count,    i.filtered_index_count,    p.nonclustered_index_count,    p.xml_index_count,    p.spatial_index_count  FROM  (    SELECT [object_id],      has_clustered_index = MIN