涉及到的系统表汇总
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()