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 Server.aspx' target='_blank'>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];

原标题：sql:SQL Server metadata queries

关键词：sql