sql:SQL Server metadata queries

最新推荐文章于 2024-11-12 14:55:58 发布
geovi
最新推荐文章于 2024-11-12 14:55:58 发布
阅读量851
点赞数
分类专栏：数据库编程
数据库编程专栏收录该内容
37 篇文章
订阅专栏
本文介绍 SQL Server 中各类目录视图的应用，包括对象目录视图、信息架构视图及兼容性视图等，展示如何通过这些视图查询数据库对象的详细信息。
-- 2012
--https://technet.microsoft.com/zh-cn/library/ms186778(v=sql.110).aspx
--对象目录视图 (Transact-SQL)
select * from sys.foreign_keys
select * from sys.foreign_key_columns

select * 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_DEFAULT
FROM PersonalCRM.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'ProjectList';
GO

SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
SELECT * FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
SELECT * FROM INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.DOMAINS
SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
SELECT * 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_guides
SELECT * 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_modules
SELECT * FROM sys.server_triggers
SELECT * FROM sys.server_trigger_events
SELECT * 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_types
SELECT * FROM sys.fulltext_index_catalog_usages
SELECT * 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_types
SELECT * FROM sys.types
SELECT * FROM sys.type_assembly_usages
SELECT * FROM sys.column_type_usages
SELECT * 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_witne
SELECT * FROM sys.database_mirroring_witnesses
SELECT * 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 视图