数据库内部对象查询

浏览数：174 / 时间：2015年06月12日

select * from sysobjects

--数据库            
select * from sys.databases               
--数据库表            
select * from sys.tables               
--数据库存储过程        
select * from sys.procedures                
--数据库触发器  
select * from sys.triggers               
--数据库视图
select * from sys.views                
--数据类型
select * from sys.types    
--...

相关对象可同步sys获得

查询所有数据库信息：

SELECT
dtb.name AS [Name],
dtb.database_id AS [ID],
CAST(case when dtb.name in (‘master‘,‘model‘,‘msdb‘,‘tempdb‘) then 1 else dtb.is_distributor end AS bit) AS [IsSystemObject],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC

View Code

查询某表的信息：

exec sp_executesql N‘SELECT
tbl.name AS [Name],
tbl.object_id AS [ID],
tbl.create_date AS [CreateDate],
tbl.modify_date AS [DateLastModified],
stbl.name AS [Owner],
CAST(case when tbl.principal_id is null then 1 else 0 end AS bit) AS [IsSchemaOwned],
SCHEMA_NAME(tbl.schema_id) AS [Schema],
CAST(
 case 
    when tbl.is_ms_shipped = 1 then 1
    when (
        select 
            major_id 
        from 
            sys.extended_properties 
        where 
            major_id = tbl.object_id and 
            minor_id = 0 and 
            class = 1 and 
            name = N‘‘microsoft_database_tools_support‘‘) 
        is not null then 1
    else 0
end          
             AS bit) AS [IsSystemObject],
CAST(OBJECTPROPERTY(tbl.object_id, N‘‘HasAfterTrigger‘‘) AS bit) AS [HasAfterTrigger],
CAST(OBJECTPROPERTY(tbl.object_id, N‘‘HasInsertTrigger‘‘) AS bit) AS [HasInsertTrigger],
CAST(OBJECTPROPERTY(tbl.object_id, N‘‘HasDeleteTrigger‘‘) AS bit) AS [HasDeleteTrigger],
CAST(OBJECTPROPERTY(tbl.object_id, N‘‘HasInsteadOfTrigger‘‘) AS bit) AS [HasInsteadOfTrigger],
CAST(OBJECTPROPERTY(tbl.object_id, N‘‘HasUpdateTrigger‘‘) AS bit) AS [HasUpdateTrigger],
CAST(OBJECTPROPERTY(tbl.object_id, N‘‘IsIndexed‘‘) AS bit) AS [HasIndex],
CAST(OBJECTPROPERTY(tbl.object_id, N‘‘IsIndexable‘‘) AS bit) AS [IsIndexable],
CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusteredIndex],
ISNULL(dstext.name,N‘‘‘‘) AS [TextFileGroup],
tbl.is_replicated AS [Replicated],
ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < @_msparam_0), 0) AS [RowCount],
tbl.uses_ansi_nulls AS [AnsiNullsStatus],
CAST(OBJECTPROPERTY(tbl.object_id,N‘‘IsQuotedIdentOn‘‘) AS bit) AS [QuotedIdentifierStatus],
CAST(0 AS bit) AS [FakeSystemTable],
CAST(case when ctt.object_id is null then 0 else 1  end AS bit) AS [ChangeTrackingEnabled],
CAST(ISNULL(ctt.is_track_columns_updated_on,0) AS bit) AS [TrackColumnsUpdatedEnabled],
tbl.lock_escalation AS [LockEscalation],
CASE WHEN ‘‘FG‘‘=dsidx.type THEN dsidx.name ELSE N‘‘‘‘ END AS [FileGroup],
CASE WHEN ‘‘PS‘‘=dsidx.type THEN dsidx.name ELSE N‘‘‘‘ END AS [PartitionScheme],
CAST(CASE WHEN ‘‘PS‘‘=dsidx.type THEN 1 ELSE 0 END AS bit) AS [IsPartitioned],
CASE WHEN ‘‘FD‘‘=dstbl.type THEN dstbl.name ELSE N‘‘‘‘ END AS [FileStreamFileGroup],
CASE WHEN ‘‘PS‘‘=dstbl.type THEN dstbl.name ELSE N‘‘‘‘ END AS [FileStreamPartitionScheme]
FROM
sys.tables AS tbl
INNER JOIN sys.database_principals AS stbl ON stbl.principal_id = ISNULL(tbl.principal_id, (OBJECTPROPERTY(tbl.object_id, ‘‘OwnerId‘‘)))
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < @_msparam_1
LEFT OUTER JOIN sys.data_spaces AS dstext  ON tbl.lob_data_space_id = dstext.data_space_id
LEFT OUTER JOIN sys.change_tracking_tables AS ctt ON ctt.object_id = tbl.object_id 
LEFT OUTER JOIN sys.data_spaces AS dsidx ON dsidx.data_space_id = idx.data_space_id
LEFT OUTER JOIN sys.tables AS t ON t.object_id = idx.object_id
LEFT OUTER JOIN sys.data_spaces AS dstbl ON dstbl.data_space_id = t.Filestream_data_space_id and idx.index_id < 2
WHERE
(tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)‘,N‘@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)‘,@_msparam_0=N‘2‘,@_msparam_1=N‘2‘,@_msparam_2=N‘ClientType‘,@_msparam_3=N‘dbo‘

View Code

查询某数据库中所有表信息：

exec sp_executesql N‘SELECT
CAST(
        serverproperty(N‘‘Servername‘‘)
       AS sysname) AS [Server_Name],
db_name() AS [Database_Name],
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
i.name AS [Index_Name],
(case ic.is_included_column when 1 then ic.index_column_id else ic.key_ordinal end) AS [ID],
clmns.name AS [Name],
ic.is_included_column AS [IsIncluded],
ic.is_descending_key AS [Descending],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N‘‘IsComputed‘‘) AS bit) AS [IsComputed]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(tbl.object_id in (389576426,325576198,149575571,21575115,213575799,85575343,1077578877,485576768,2105058535))
ORDER BY
[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Index_Name] ASC,[ID] ASC‘,N‘@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)‘,@_msparam_0=N‘0‘,@_msparam_1=N‘0‘

View Code

查询数据库所有表及字段明细：

SELECT
CAST(
        serverproperty(N‘Servername‘)
       AS sysname) AS [Server_Name],
db_name() AS [Database_Name],
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
st.name AS [Statistic_Name],
sic.stats_column_id AS [ID],
COL_NAME(sic.object_id, sic.column_id) AS [Name]
FROM
sys.tables AS tbl
INNER JOIN sys.stats st ON st.object_id=tbl.object_id
INNER JOIN sys.stats_columns sic ON sic.stats_id=st.stats_id AND sic.object_id=st.object_id
WHERE 1=1
--(tbl.object_id in (389576426,325576198,149575571,21575115,213575799,85575343,1077578877,485576768,2105058535))
ORDER BY
[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Statistic_Name] ASC,[ID] ASC

View Code

查询指定表结构：

exec sp_executesql N‘SELECT
clmns.column_id AS [ID],
clmns.name AS [Name],
clmns.is_nullable AS [Nullable],
CAST(ISNULL(cik.index_column_id, 0) AS bit) AS [InPrimaryKey],
clmns.is_identity AS [Identity],
usrt.name AS [DataType],
ISNULL(baset.name, N‘‘‘‘) AS [SystemType],
CAST(CASE WHEN baset.name IN (N‘‘nchar‘‘, N‘‘nvarchar‘‘) AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],
CAST(clmns.precision AS int) AS [NumericPrecision],
CAST(clmns.scale AS int) AS [NumericScale],
ISNULL(xscclmns.name, N‘‘‘‘) AS [XmlSchemaNamespace],
ISNULL(s2clmns.name, N‘‘‘‘) AS [XmlSchemaNamespaceSchema],
ISNULL( (case clmns.is_xml_document when 1 then 2 else 1 end), 0) AS [XmlDocumentConstraint],
sclmns.name AS [DataTypeSchema]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.indexes AS ik ON ik.object_id = clmns.object_id and 1=ik.is_primary_key
LEFT OUTER JOIN sys.index_columns AS cik ON cik.index_id = ik.index_id and cik.column_id = clmns.column_id and cik.object_id = clmns.object_id and 0 = cik.is_included_column
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) 
LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns ON xscclmns.xml_collection_id = clmns.xml_collection_id
LEFT OUTER JOIN sys.schemas AS s2clmns ON s2clmns.schema_id = xscclmns.schema_id
LEFT OUTER JOIN sys.schemas AS sclmns ON sclmns.schema_id = usrt.schema_id
WHERE
(tbl.name=@_msparam_0 and SCHEMA_NAME(tbl.schema_id)=@_msparam_1)
ORDER BY
[ID] ASC‘,N‘@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)‘,@_msparam_0=N‘CompanyInfo‘,@_msparam_1=N‘dbo‘