sql:SQL Server metadata queries

-- 2012--https://technet.microsoft.com/zh-cn/library/ms186778(v=sql.110).aspx--对象目录视图 (Transact-SQL)select * from sys.foreign_keysselect * from sys.foreign_key_columnsselect * from sys.all_columns select * from sys.all_objects select * from sys.all_parameters select * from sys.all_sql_modules select * from sys.all_views select * from sys.allocation_units select * from sys.assembly_modules select * from sys.check_constraints select * from sys.column_store_dictionaries select * from sys.column_store_segments select * from sys.columns select * from sys.computed_columns select * from sys.default_constraints select * from sys.events select * from sys.event_notifications select * from sys.event_notification_event_types select * from sys.extended_procedures select * from sys.foreign_keys select * from sys.foreign_key_columns select * from sys.function_order_columns select * from sys.identity_columns select * from sys.indexes select * from sys.index_columns select * from sys.internal_tables select * from sys.key_constraints select * from sys.module_assembly_usages select * from sys.numbered_procedures select * from sys.numbered_procedure_parameters select * from sys.objects select * from sys.parameters select * from sys.partitions select * from sys.plan_guides select * from sys.procedures select * from sys.sequences select * from sys.server_assembly_modules select * from sys.server_events select * from sys.server_event_notifications select * from sys.server_sql_modules select * from sys.server_triggers select * from sys.server_trigger_events select * from sys.sql_dependencies select * from sys.sql_expression_dependencies select * from sys.sql_modules select * from sys.stats select * from sys.stats_columns select * from sys.synonyms select * from sys.system_columns select * from sys.system_objects select * from sys.system_parameters select * from sys.system_sql_modules select * from sys.system_views select * from sys.table_types select * from sys.tables select * from sys.trigger_event_types select * from sys.trigger_events select * from sys.triggers select * from sys.views --信息架构视图 (Transact-SQL)SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULTFROM PersonalCRM.INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = N'ProjectList';GOSELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTSSELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTSSELECT * FROM INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGESELECT * FROM INFORMATION_SCHEMA.ROUTINESSELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGESSELECT * FROM INFORMATION_SCHEMA.ROUTINE_COLUMNSSELECT * FROM INFORMATION_SCHEMA.COLUMNSSELECT * FROM INFORMATION_SCHEMA.SCHEMATASELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGESELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTSSELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGESELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGESSELECT * FROM INFORMATION_SCHEMA.DOMAIN_CONSTRAINTSSELECT * FROM INFORMATION_SCHEMA.TABLESSELECT * FROM INFORMATION_SCHEMA.DOMAINSSELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGESELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGESELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGESELECT * FROM INFORMATION_SCHEMA.PARAMETERSSELECT * FROM INFORMATION_SCHEMA.VIEWS--兼容性视图 (Transact-SQL)SELECT * FROM sys.sysaltfiles SELECT * FROM sys.syscacheobjects SELECT * FROM sys.syscharsets SELECT * FROM sys.syscolumns SELECT * FROM sys.syscomments SELECT * FROM sp_configure SELECT * FROM sys.sysconstraints SELECT * FROM sys.syscurconfigs SELECT * FROM sys.sysdatabases SELECT * FROM sys.sysdepends SELECT * FROM sys.sysdevices SELECT * FROM sys.sysfilegroups SELECT * FROM sys.sysfiles SELECT * FROM sys.sysforeignkeys SELECT * FROM sys.sysfulltextcatalogs SELECT * FROM sys.sysindexes SELECT * FROM sys.sysindexkeys SELECT * FROM sys.syslanguages SELECT * FROM sys.syslockinfo SELECT * FROM sys.syslogins SELECT * FROM sys.sysmembers SELECT * FROM sys.sysmessages SELECT * FROM sys.sysobjects SELECT * FROM sys.sysoledbusers SELECT * FROM sys.sysperfinfo SELECT * FROM sys.syspermissions SELECT * FROM sys.sysprocesses SELECT * FROM sys.sysprotects SELECT * FROM sys.sysreferences SELECT * FROM sys.sysremotelogins SELECT * FROM sys.sysservers SELECT * FROM sys.systypes SELECT * FROM sys.sysusers --2005--https://technet.microsoft.com/zh-cn/library/ms177862(v=sql.90).aspx--系统视图 (Transact-SQL)SELECT * FROM sys.schemas--目录视图 (Transact-SQL)--对象目录视图 (Transact-SQL)SELECT * FROM sys.allocation_units SELECT * FROM sys.assembly_modules SELECT * FROM sys.check_constraints SELECT * FROM sys.columns SELECT * FROM sys.computed_columns SELECT * FROM sys.default_constraints SELECT * FROM sys.events SELECT * FROM sys.event_notifications SELECT * FROM sys.event_notification_event_types SELECT * FROM sys.extended_procedures SELECT * FROM sys.foreign_keys SELECT * FROM sys.foreign_key_columns SELECT * FROM sys.fulltext_indexes SELECT * FROM sys.fulltext_index_columns SELECT * FROM sys.identity_columns SELECT * FROM sys.indexes SELECT * FROM sys.index_columns SELECT * FROM sys.internal_tables SELECT * FROM sys.key_constraints SELECT * FROM sys.module_assembly_usages SELECT * FROM sys.numbered_procedures SELECT * FROM sys.numbered_procedure_parameters SELECT * FROM sys.objects SELECT * FROM sys.parameters SELECT * FROM sys.partitions SELECT * FROM sys.plan_guidesSELECT * FROM sys.procedures SELECT * FROM sys.server_assembly_modules SELECT * FROM sys.server_events SELECT * FROM sys.server_event_notifications SELECT * FROM sys.server_sql_modulesSELECT * FROM sys.server_triggersSELECT * FROM sys.server_trigger_eventsSELECT * FROM sys.service_queues SELECT * FROM sys.sql_dependencies SELECT * FROM sys.sql_modules SELECT * FROM sys.stats SELECT * FROM sys.stats_columns SELECT * FROM sys.synonyms SELECT * FROM sys.tables SELECT * FROM sys.triggers SELECT * FROM sys.trigger_events SELECT * FROM sys.views --数据空间目录视图和全文目录视图 (Transact-SQL)SELECT * FROM sys.data_spaces SELECT * FROM sys.destination_data_spaces SELECT * FROM sys.filegroups SELECT * FROM sys.fulltext_catalogs SELECT * FROM sys.fulltext_document_typesSELECT * FROM sys.fulltext_index_catalog_usagesSELECT * FROM sys.partition_schemes --数据库和文件目录视图 (Transact-SQL)SELECT * FROM sys.backup_devices SELECT * FROM sys.databases SELECT * FROM sys.database_files SELECT * FROM sys.database_mirroring SELECT * FROM sys.database_recovery_status SELECT * FROM sys.master_files --CLR 程序集目录视图 (Transact-SQL)SELECT * FROM sys.assemblies SELECT * FROM sys.assembly_files SELECT * FROM sys.assembly_references --分区函数目录视图 (Transact-SQL)SELECT * FROM sys.partition_functions SELECT * FROM sys.partition_parameters SELECT * FROM sys.partition_range_values --标量类型目录视图 (Transact-SQL)SELECT * FROM sys.assembly_typesSELECT * FROM sys.typesSELECT * FROM sys.type_assembly_usagesSELECT * FROM sys.column_type_usagesSELECT * FROM sys.parameter_type_usages--安全性目录视图 (Transact-SQL)SELECT * FROM sys.asymmetric_keys SELECT * FROM sys.certificates SELECT * FROM sys.credentials SELECT * FROM sys.crypt_properties SELECT * FROM sys.database_permissions SELECT * FROM sys.database_principals SELECT * FROM sys.database_role_members SELECT * FROM sys.key_encryptions SELECT * FROM sys.master_key_passwords SELECT * FROM sys.openkeys SELECT * FROM sys.securable_classes SELECT * FROM sys.server_permissions SELECT * FROM sys.server_principals SELECT * FROM sys.server_role_members SELECT * FROM sys.sql_logins SELECT * FROM sys.symmetric_keys SELECT * FROM sys.system_components_surface_area_configuration --服务器范围内的配置目录视图 (Transact-SQL)SELECT * FROM sys.configurations SELECT * FROM sys.fulltext_languages SELECT * FROM sys.traces SELECT * FROM sys.trace_categories SELECT * FROM sys.trace_columns SELECT * FROM sys.trace_events SELECT * FROM sys.trace_event_bindings SELECT * FROM sys.trace_subclass_values --架构目录视图 (Transact-SQL)SELECT * FROM sys.schemas --XML 架构（XML 类型系统）目录视图 (Transact-SQL)SELECT * FROM sys.column_xml_schema_collection_usages SELECT * FROM sys.parameter_xml_schema_collection_usages SELECT * FROM sys.xml_schema_attributes SELECT * FROM sys.xml_schema_component_placements SELECT * FROM sys.xml_schema_components SELECT * FROM sys.xml_schema_elements SELECT * FROM sys.xml_schema_facets SELECT * FROM sys.xml_indexes SELECT * FROM sys.xml_schema_model_groups SELECT * FROM sys.xml_schema_collections SELECT * FROM sys.xml_schema_namespaces SELECT * FROM sys.xml_schema_types SELECT * FROM sys.xml_schema_wildcard_namespaces SELECT * FROM sys.xml_schema_wildcards --数据库镜像见证服务器目录视图 (Transact-SQL)SELECT * FROM  sys.database_mirroring_witneSELECT * FROM sys.database_mirroring_witnessesSELECT * FROM sys.database_mirroring_endpoints--端点目录视图 (Transact-SQL)SELECT * FROM sys.database_mirroring_endpoints SELECT * FROM sys.endpoints SELECT * FROM sys.endpoint_webmethods SELECT * FROM sys.http_endpoints SELECT * FROM sys.service_broker_endpoints SELECT * FROM sys.soap_endpoints SELECT * FROM sys.tcp_endpoints SELECT * FROM sys.via_endpoints ---扩展属性目录视图 (Transact-SQL)SELECT * FROM sys.extended_properties --链接服务器目录视图 (Transact-SQL)SELECT * FROM sys.linked_logins SELECT * FROM sys.remote_logins SELECT * FROM sys.servers --（错误）消息目录视图 (Transact-SQL)SELECT * FROM sys.messages--Service Broker 目录视图 (Transact-SQL)SELECT * FROM sys.conversation_endpoints SELECT * FROM sys.conversation_groups SELECT * FROM sys.message_type_xml_schema_collection_usages SELECT * FROM sys.remote_service_bindings SELECT * FROM sys.routes SELECT * FROM sys.service_contracts SELECT * FROM sys.service_contract_message_usages SELECT * FROM sys.service_contract_usages SELECT * FROM sys.service_message_types SELECT * FROM sys.service_queue_usages SELECT * FROM sys.services SELECT * FROM sys.transmission_queue --兼容性视图 (Transact-SQL)SELECT * FROM sys.sysaltfiles SELECT * FROM sys.syscacheobjects SELECT * FROM sys.syscharsets SELECT * FROM sys.syscolumns SELECT * FROM sys.syscomments SELECT * FROM sys.sysconfigures SELECT * FROM sys.sysconstraints SELECT * FROM sys.syscurconfigs SELECT * FROM sys.sysdatabases SELECT * FROM sys.sysdepends SELECT * FROM sys.sysdevices SELECT * FROM sys.sysfilegroups SELECT * FROM sys.sysfiles SELECT * FROM sys.sysforeignkeys SELECT * FROM sys.sysfulltextcatalogs SELECT * FROM sys.sysindexes SELECT * FROM sys.sysindexkeys SELECT * FROM sys.syslanguages SELECT * FROM sys.syslockinfo SELECT * FROM sys.syslogins SELECT * FROM sys.sysmembers SELECT * FROM sys.sysmessages SELECT * FROM sys.sysobjects SELECT * FROM sys.sysperfinfo SELECT * FROM sys.syspermissions SELECT * FROM sys.sysprocesses SELECT * FROM sys.sysprotects SELECT * FROM sys.sysreferences SELECT * FROM sys.sysremotelogins SELECT * FROM sys.sysservers SELECT * FROM sys.systypes SELECT * FROM sys.sysusers --数据库邮件视图 (Transact-SQL)sysmail_allitems sysmail_event_log sysmail_faileditems sysmail_mailattachments sysmail_sentitems sysmail_unsentitems --动态管理视图和函数--信息架构视图 (Transact-SQL)SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS SELECT * FROM INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES SELECT * FROM INFORMATION_SCHEMA.COLUMNS SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE SELECT * FROM INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS SELECT * FROM INFORMATION_SCHEMA.DOMAINS SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  --主键SELECT * FROM INFORMATION_SCHEMA.PARAMETERS SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS --外鍵SELECT * FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS SELECT * FROM INFORMATION_SCHEMA.ROUTINES SELECT * FROM INFORMATION_SCHEMA.SCHEMATA SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES SELECT * FROM INFORMATION_SCHEMA.TABLES SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE SELECT * FROM INFORMATION_SCHEMA.VIEWS --复制视图 (Transact-SQL)IHextendedArticleView IHextendedSubscriptionView IHsyscolumns MSdatatype_mappings MSdistribution_status sysarticlecolumns--（系统视图）sysarticles--（系统视图）sysdatatypemappings sysextendedarticlesview sysmergeextendedarticlesview sysmergepartitioninfoview syspublications--（系统视图）syssubscriptions--（系统视图）--Notification Services 视图