SQLServer 表结构相关查询（快速了解数据库）
时间：2021-07-01 10:21:17
帮助过：5人阅读
表结构查询
SELECT
表名 = case when a.colorder
=1 then d.name
else ‘‘ end,
表说明 = case when a.colorder
=1 then isnull(f.value,
‘‘)
else ‘‘ end,
字段序号 = a.colorder,
字段名 = a.name,
标识 = case when COLUMNPROPERTY( a.id,a.name,
‘IsIdentity‘)
=1 then ‘√‘else ‘‘ end,
主键 = case when exists(
SELECT 1 FROM sysobjects
where xtype
=‘PK‘ and parent_obj
=a.id
and name
in (
SELECT name
FROM sysindexes
WHERE indid
in(
SELECT indid
FROM sysindexkeys
WHERE id
= a.id
AND colid
=a.colid)))
then ‘√‘ else ‘‘ end,
类型 = b.name,
占用字节数 = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,
‘PRECISION‘),
小数位数 = isnull(
COLUMNPROPERTY(a.id,a.name,
‘Scale‘),
0),
允许空 = case when a.isnullable
=1 then ‘√‘else ‘‘ end,
默认值 = isnull(e.
text,
‘‘),
字段说明 = isnull(g.
[value],
‘‘)
FROM syscolumns a
left join systypes b
on a.xusertype
=b.xusertype
inner join sysobjects d
on a.id
=d.id
and d.xtype
=‘U‘ and d.name
<>‘dtproperties‘
left join syscomments e
on a.cdefault
=e.id
left join sys.extended_properties g
on a.id
=g.major_id
and a.colid
=g.minor_id
left join sys.extended_properties f
on d.id
=f.major_id
and f.minor_id
=0
where d.name
=‘tableName‘ --如果只查询指定表,加上此条件
order by a.id,a.colorder
-------------------------------------------------------------------------------------------------
--查看数据库中所有外键
select oMain.name
AS [主表名称]
,oSub.name AS [子表名称]
,fk.name AS [外键名称]
,MainCol.name AS [主表列名]
,SubCol.name AS [子表列名]
from sys.foreign_keys fk
JOIN sys.all_objects oSub
ON (fk.parent_object_id
= oSub.
object_id)
JOIN sys.all_objects oMain
ON (fk.referenced_object_id
= oMain.
object_id)
JOIN sys.foreign_key_columns fkCols
ON (fk.
object_id = fkCols.constraint_object_id)
JOIN sys.columns SubCol
ON (oSub.
object_id = SubCol.
object_id
AND fkCols.parent_column_id
= SubCol.column_id)
JOIN sys.columns MainCol
ON (oMain.
object_id = MainCol.
object_id
AND fkCols.referenced_column_id
= MainCol.column_id)
------------------------------------------------------------------------------------
--(导出扩展属性脚本)
SELECT 表名
= d.name,字段名
= a.name, 字段说明
= isnull(g.
[value],
‘‘)
,‘EXEC sys.sp_addextendedproperty @name=N‘‘MS_Description‘‘, @value=N‘‘‘+CONVERT(
VARCHAR(
MAX),g.
[value])
+‘‘‘,@level0type=N‘‘SCHEMA‘‘,@level0name=N‘‘dbo‘‘,@level1type=N‘‘TABLE‘‘,@level1name=N‘‘‘+CONVERT(
VARCHAR(
MAX),d.name)
+‘‘‘,@level2type=N‘‘COLUMN‘‘,@level2name=N‘‘‘+CONVERT(
VARCHAR(
MAX),a.name)
+‘‘‘‘
FROM syscolumns a
left join systypes b
on a.xusertype
=b.xusertype
inner join sysobjects d
on a.id
=d.id
and d.xtype
=‘U‘ and d.name
<>‘dtproperties‘
left join syscomments e
on a.cdefault
=e.id
left join sys.extended_properties g
on a.id
=g.major_id
and a.colid
=g.minor_id
WHERE g.
[value] IS NOT NULL
ORDER BY d.name,a.name
----------------------------------------------------------------------
--当前数据库表大小及行数
SELECT SCHEMA_NAME(tbl.schema_id)
[Schema],tbl.name
AS [TableName],
(CAST(
ISNULL((
select 8 * 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)
*1.0/1024 AS DECIMAL(
18,
3)))
AS [DataSpaceUsed(MB)],SI.
[rows]
FROM sys.tables
AS tbl
LEFT JOIN sys.sysindexes si
ON tbl.
object_id=si.id
AND si.indid
IN(
0,
1)
ORDER BY [Schema],
[DataSpaceUsed(MB)] DESC
-----------------------------------------------------------------------------------------
-- 查看表中的自增列是否为主键
SELECT
表名= D.NAME,
列名= A.NAME,
是否自增= CASE WHEN COLUMNPROPERTY( A.ID,A.NAME,
‘ISIDENTITY ‘)
=1 THEN ‘√‘ELSE ‘‘ END,
主键= CASE WHEN EXISTS(
SELECT 1 FROM SYSOBJECTS
WHERE XTYPE
= ‘PK ‘ AND PARENT_OBJ
=A.ID
AND NAME
IN (
SELECT NAME
FROM SYSINDEXES
WHERE INDID
IN(
SELECT INDID
FROM SYSINDEXKEYS
WHERE ID
= A.ID
AND COLID
=A.COLID)))
THEN ‘√‘ ELSE ‘‘ END
FROM SYSCOLUMNS A
LEFT JOIN SYSTYPES B
ON A.XUSERTYPE
=B.XUSERTYPE
INNER JOIN SYSOBJECTS D
ON A.ID
=D.ID
AND D.XTYPE
= ‘U‘ AND D.NAME
<> ‘DTPROPERTIES ‘
where COLUMNPROPERTY( A.ID,A.NAME,
‘ISIDENTITY ‘)
=1
------------------------------------------------------------------
--各表对象下的其他对象
select t1.
[object_id],t1.
[type],t1.name,t2.
[object_id],t2.
[type],t2.name
from sys.objects t1
inner join sys.objects t2
on t1.
[object_id]=t2.parent_object_id
order by t1.
[type],t1.name,t2.
[type],t2.name
select t1.id,t1.xtype,t1.name,t2.id,t2.xtype,t2.name
from sys.sysobjects t1
inner join sys.sysobjects t2
on t1.id
=t2.parent_obj
order by t1.xtype,t1.name,t2.xtype,t2.name
-----------------------------------------------------------------------------
--唯一键约束
SELECT tbl.name tab,i.name
AS [Name]
FROM sys.tables
AS tbl
INNER JOIN sys.indexes
AS i
ON (i.index_id
> 0 and i.is_hypothetical
= 0)
AND (i.
object_id=tbl.
object_id)
WHERE (i.is_primary_key
+ 2*i.is_unique_constraint
=2)
and SCHEMA_NAME(tbl.schema_id)
=‘dbo‘
ORDER BY [Name] ASC
------------------------------------------------------------------------------------
--查看数据库约束
SELECT OBJECT_NAME(parent_object_id)
as TableName,name,definition
FROM sys.default_constraints
ORDER BY TableName,name
---------------------------------------------------------------------------------------
--表各列约束
select OBJECT_NAME(t2.
object_id)
as TabName,t2.name
as ColumnName,t1.name
as [Constraint]
from sys.default_constraints t1
inner join sys.columns t2
on t1.parent_object_id
=t2.
object_id and t1.parent_column_id
=t2.column_id
order by TabName,ColumnName,
[Constraint]
----------------------------------------------------------------------------------
-- 当前数据库文件增长设置情况
SELECT Name, FileName
, CAST((Size
* 8 / 1024)
AS varchar(
10))
+ ‘MB‘ AS FileSize
, MaxSize = CASE MaxSize
WHEN -1 THEN ‘Unlimited‘ ELSE CAST((Maxsize
/ 128)
AS varchar(
10))
+ ‘MB‘ END
FROM sys.sysfiles;
-- 所有数据库文件增长设置情况
select DB_NAME(database_id)
as dbName,
file_id,(size
*8/1024)
as [size(mb)]
,case when is_percent_growth
= 1 then ‘10%‘ else CONVERT(
varchar(
10),growth
*8/1024)
+‘M‘ end as growth
,type_desc,physical_name
from sys.master_files
where state
= 0 --and database_id=DB_id()
-----------------------------------------------------------------------------------------------------------------
--数据库的一些关键属性
SELECT db.
[name] AS [Database Name], db.recovery_model_desc
AS [Recovery Model],
db.log_reuse_wait_desc AS [Log Reuse Wait Description],
ls.cntr_value AS [Log Size (KB)], lu.cntr_value
AS [Log Used (KB)],
CAST(
CAST(lu.cntr_value
AS FLOAT)
/ CAST(ls.cntr_value
AS FLOAT)
AS DECIMAL(
18,
2))
*
AS [Log Used %], db.
[compatibility_level] AS [DB Compatibility Level],
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on,
db.is_auto_update_stats_on, db.is_auto_update_stats_async_on,
db.is_parameterization_forced,
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
is_auto_shrink_on, is_auto_close_on
FROM sys.databases
AS db
WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters
AS lu
WITH (NOLOCK)
ON db.name
= lu.instance_name
INNER JOIN sys.dm_os_performance_counters
AS ls
WITH (NOLOCK)
ON db.name
= ls.instance_name
WHERE lu.counter_name
LIKE N
‘Log File(s) Used Size (KB)%‘
AND ls.counter_name
LIKE N
‘Log File(s) Size (KB)%‘
AND ls.cntr_value
> 0 OPTION (RECOMPILE);
----------------------------------------------------------------------------------------
--最近一周内数据库备份情况
SELECT user_name AS [User]
,server_name AS [Server]
,database_name AS [Database]
,recovery_model AS RecoveryModel
,case type
when ‘D‘ then ‘数据库‘
when ‘I‘ then ‘差异数据库‘
when ‘L‘ then ‘日志‘
when ‘F‘ then ‘文件或文件组‘
when ‘G‘ then ‘差异文件‘
when ‘P‘ then ‘部分‘
when ‘Q‘ then ‘差异部分‘ else type
end as [backupType]
,convert(numeric(
10,
2),backup_size
/1024/1024)
as [Size(M)]
,backup_start_date AS backupStartTime
,backup_finish_date as backupFinishTime
,name
,expiration_date
from msdb.dbo.backupset
where backup_start_date
>= DATEADD(D,
-7,
GETDATE())
-----------------------------------------------------------------------
-- 作业启用情况和所有者
select a.job_id,a.name,a.enabled,b.name
from msdb.dbo.sysjobs a
inner join master.sys.syslogins b
on a.owner_sid
=b.sid
and a.owner_sid
<>‘0x01‘
order by a.name
-- 更改作业所有者
EXEC msdb.dbo.sp_update_job
@job_id=N
‘job_id‘,
@owner_login_name=N
‘sa‘
----------------------------------------------------------------------------------------------
-- 索引 主键/类型/列 情况
;
with tb
as(
SELECT tbl.name
AS TableName,i.name
AS IndexName,clmns.name
AS ColumName,i.is_primary_key
AS isPrimaryKey,i.type_desc
FROM sys.tables
AS tbl
INNER JOIN sys.indexes
AS i
ON (i.index_id
> 0 and i.is_hypothetical
= 0)
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 SCHEMA_NAME(tbl.schema_id)
= N
‘dbo‘
)
SELECT DISTINCT TableName,IndexName,isPrimaryKey,type_desc
,STUFF((
SELECT ‘,‘+ColumName
FROM tb B
WHERE A.TableName
=B.TableName
AND A.IndexName
=B.IndexName
FOR XML PATH(
‘‘)),
1,
1,
‘‘)
AS ColumName
FROM tb A
ORDER BY TableName,IndexName,isPrimaryKey,type_desc
----------------------------------------------------------------------------------------------------------------------------------------------------------
--表主键对应的列
SELECT OBJECT_NAME(C.id)
AS TAB,B.name,A.name
AS PrimaryKey ,E.type_desc,fill_factor
FROM SYSCOLUMNS A,SYSOBJECTS B,SYSINDEXES C,SYSINDEXKEYS D , SYS.INDEXES E
WHERE B.xtype
= ‘PK‘
AND B.parent_obj
= A.id
AND C.id
= A.id
AND B.name
= C.name
AND D.id
= A.id
AND D.indid
= C.indid
AND A.colid
= D.colid
AND B.name
=E.name
ORDER BY TAB,B.name,PrimaryKey
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(
OBJECT_ID(CONSTRAINT_NAME),
‘ISPRIMARYKEY‘)
=1
--AND TABLE_NAME=‘TABLE_NAME‘
-------------------------------------------------------------------------------------------------------
--所有表索引对应的键列和包含列
SELECT OBJECT_NAME(t1.id)
as tab,t1.name
,STUFF((
SELECT ‘,‘+t3.name
FROM sys.sysindexkeys t2,sys.syscolumns t3
WHERE t1.indid
=t2.indid
and t1.id
=t2.id
and t2.id
=t3.id
and t2.colid
=t3.colid
and t2.keyno
<> 0
FOR XML PATH(
‘‘)),
1,
1,
‘‘)
AS IndexCols
,STUFF((
SELECT ‘,‘+t3.name
FROM sys.sysindexkeys t2,sys.syscolumns t3
WHERE t1.indid
=t2.indid
and t1.id
=t2.id
and t2.id
=t3.id
and t2.colid
=t3.colid
and t2.keyno
= 0
FOR XML PATH(
‘‘)),
1,
1,
‘‘)
AS IncludeCols
FROM sys.sysindexes t1
WHERE t1.root
is not null
AND EXISTS(
SELECT * FROM sys.tables t4
WHERE t1.id
=t4.
object_id)
ORDER BY tab,IndexCols
------------------------------------------------------------------------------------------------------------
-- 查看表分区情况
select OBJECT_NAME(
object_id)
as tab,
COUNT(partition_number)
as part
from sys.partitions
where index_id
in(
0,
1)
and OBJECT_NAME(
object_id)
not like ‘conflict%‘
and OBJECT_NAME(
object_id)
not like ‘sys%‘
group by object_id order by tab
-------------------------------------------------------------------------------------------
-- 查看表备注信息
select distinct
表名 = case when a.colorder
=1 then d.name
else ‘‘ end
,表说明 = case when a.colorder
=1 then isnull(f.value,
‘‘)
else ‘‘ end
from syscolumns a
inner join sysobjects d
on a.id
=d.id
and d.xtype
=‘U‘ and d.name
<>‘dtproperties‘
inner join sys.extended_properties f
on d.id
=f.major_id
where f.minor_id
=0
--and CHARINDEX(‘‘,convert(varchar(max),f.value))<>0
-------------------------------------------------------------------------------------------
-- 查看表中各列的属性及创建扩展属性脚本（默认架构dbo）
select o.name,c.name,p.name,p.value
,N‘EXEC sys.sp_addextendedproperty @name=N‘‘‘+p.name
+ N
‘‘‘, @value=N‘‘‘+convert(
nvarchar(
4000),p.value)
+N
‘‘‘ , @level0type=N‘‘SCHEMA‘‘,@level0name=N‘‘dbo‘‘, @level1type=N‘‘TABLE‘‘,@level1name=N‘‘‘
+o.name
+ N
‘‘‘, @level2type=N‘‘COLUMN‘‘,@level2name=N‘‘‘+c.name
+ N
‘‘‘‘ as script_addextendedproperty
from sys.sysobjects o
inner join sys.syscolumns c
on o.id
= c.id
inner join sys.extended_properties p
on c.id
=p.major_id
and c.colid
=p.minor_id
where o.xtype
= N
‘U‘ --and o.name = ‘tableName‘
-------------------------------------------------------------------------------------------
-- 查看对象定义脚本
--exec sp_helptext ‘object_name‘
SELECT * from INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE
= ‘FUNCTION‘ AND ROUTINE_NAME
=‘‘
SELECT *
