http://www.mssqltips.com/sqlservertip/3449/making-sql-server-metadata-queries-easier-with-these-new-viewshttp://www.sql-server-helper.com/fu ...
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
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们:
admin#shaoqun.com
(#换成@)。