涉及到的系统表汇总

sys.databases

sys.objects

sys.indexes

sys.tables

sys.columns

sys.data_spaces

sys.partitions

sys.allocation_units

sys.fulltext_indexes

sys.fulltext_index_columns

sys.fn_helpcollations()

 

=======确定全文索引是否可用：

SELECT

dtb.is_fulltext_enabled AS [IsFullTextEnabled]

FROM

master.sys.databases AS dtb

WHERE

(dtb.name=N'DLib')

 

=======表中全文索引的几本信息：

SELECT

CAST(fti.is_enabled AS bit) AS [IsEnabled],

OBJECTPROPERTY(fti.object_id,'TableFullTextPopulateStatus') AS [PopulationStatus],

(case change_tracking_state when 'M' then 1 when 'A' then 2 else 0 end) AS [ChangeTracking]

FROM

sys.tables AS tbl

INNER JOIN sys.fulltext_indexes AS fti ON fti.object_id=tbl.object_id

WHERE

(tbl.name=N'AreaInfo' and SCHEMA_NAME(tbl.schema_id)=N'dbo')

 

=======获得全文索引列：

SELECT

col.name AS [Name]

FROM

sys.tables AS tbl

INNER JOIN sys.fulltext_indexes AS fti ON fti.object_id=tbl.object_id

INNER JOIN sys.fulltext_index_columns AS icol ON icol.object_id=fti.object_id

INNER JOIN sys.columns AS col ON col.object_id = icol.object_id and col.column_id = icol.column_id

WHERE

(tbl.name=N'AreaInfo' and SCHEMA_NAME(tbl.schema_id)=N'dbo')

 

=======获得获得服务器名：

select SERVERPROPERTY(N'servername')

 

=======排序规则信息：

SELECT

dtb.collation_name AS [Collation],

dtb.name AS [DatabaseName2]

FROM master.sys.databases AS dtb

WHERE (dtb.name=N'master')

SELECT

CAST(COLLATIONPROPERTY(name, 'LCID') AS int) AS [LocaleID]

FROM sys.fn_helpcollations() cl

WHERE (cl.name=N'Chinese_PRC_CI_AS')

 

=======获取兼容级别：

SELECT

dtb.compatibility_level AS [CompatibilityLevel],

dtb.name AS [DatabaseName2]

FROM master.sys.databases AS dtb

WHERE (dtb.name=N'DLib')

 

=======表名和架构名：

SELECT

SCHEMA_NAME(tbl.schema_id) AS [Schema],

tbl.name AS [Name]

FROM sys.tables AS tbl

WHERE (tbl.name=N'AreaInfo' and SCHEMA_NAME(tbl.schema_id)=N'dbo')

 

=======Version：

SELECT

(@@microsoftversion / 0x1000000) & 0xff AS [VersionMajor]

 

=======创建时间、所属文件组等信息：

SELECT

tbl.name AS [Name],

tbl.object_id AS [ID],

tbl.create_date AS [CreateDate],

tbl.modify_date AS [DateLastModified],

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 < 2), 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],

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]

FROM

sys.tables AS tbl

INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2

LEFT OUTER JOIN sys.data_spaces AS dstext  ON tbl.lob_data_space_id = dstext.data_space_id

LEFT OUTER JOIN sys.data_spaces AS dsidx ON dsidx.data_space_id = idx.data_space_id

WHERE

(tbl.name=N'AreaInfo' and SCHEMA_NAME(tbl.schema_id)=N'dbo')

 

=======数据空间已使用的情况：

declare @PageSize float 

select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'

SELECT

ISNULL((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

FROM sys.indexes as i

JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

JOIN sys.allocation_units as a ON a.container_id = p.partition_id

where i.object_id = tbl.object_id),0.0) AS [DataSpaceUsed]

FROM

sys.tables AS tbl

WHERE

(tbl.name=N'AreaInfo' and SCHEMA_NAME(tbl.schema_id)=N'dbo')

 

=======索引空间已使用的情况：

declare @PageSize float

select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'

SELECT

ISNULL((select @PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

FROM sys.indexes as i

JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id

JOIN sys.allocation_units as a ON a.container_id = p.partition_id

where i.object_id = tbl.object_id),0.0) AS [IndexSpaceUsed]

FROM

sys.tables AS tbl

WHERE

(tbl.name=N'AreaInfo' and SCHEMA_NAME(tbl.schema_id)=N'dbo')

 

=======当前连接用户：

select suser_sname()