mssql sqlserver 快速了解数据库的方法分享


摘要:
当我们接触一个陌生的数据库时,我们可以通过以下脚本生成数据库中主要信息列表,
通过列表信息快速了解数据库的相关信息,如下所示:



一、快速获取表结构

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),
[列是否允许null] = 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=’表名’ –查询指定表
order by a.id,a.colorder
—————————————————————————

二、快速获取当前数据库下所有外键信息

–查看数据库中所有外键
select oMain.name AS [Main表名称]
,oSub.name AS [Sub表名称]
,fk.name AS [外键名称]
,MainCol.name AS [Main表列名]
,SubCol.name AS [Sub表列名]
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) [架构名称],tbl.name AS [表名称],
(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 [架构名称] 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 [表名称]=tbl.name,[约束名称]=i.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 tbl.name ASC
———————————————————————————

八、数据库默认值约束

SELECT OBJECT_NAME(parent_object_id) as [表名称],name,definition
FROM sys.default_constraints ORDER BY [表名称],name
———————————————————————————

九、数据表各列约束情况

select OBJECT_NAME(t2.object_id) as [数据表],t2.name as [列名],t1.name as [约束名]
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 [数据表]
———————————————————————————

十、数据库增长情况分析

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 top 100 user_name AS [操作人]
,server_name AS [服务器名称]
,database_name AS [数据库名称]
,recovery_model AS [恢复模式]
,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
order by backup_start_date desc
———————————————————————————

十二、数据库作业运行历史查看

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
———————————————————————————

十三、表分区查看

— 查看表分区情况
select OBJECT_NAME(object_id) as [表名],COUNT(partition_number) as [分区]
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 [表名]
———————————————————————————

十四、获取数据库对象定义信息

— 查看对应定义脚本信息
exec sp_helptext ‘对象名称’

SELECT o.name,o.type,o.create_date,o.modify_date,sm.definition
FROM sys.sql_modules sm inner join sys.objects o on sm.object_id=o.object_id
ORDER BY o.type,o.name

—数据库下触发器查看
SELECT * from sys.sql_modules M WHERE EXISTS(SELECT * from sys.triggers T WHERE M.object_id=T.object_id)