运维脚本--快速了解数据库信息
- -- 表结构查询
- 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
- --各字段元数据(数据字典,MSSQL 2012及以上版本)
- select * from sys.dm_exec_describe_first_result_set (N'SELECT * FROM dbo.TabName', NULL, 0)
- GO
- --查看数据库中所有外键
- 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 distinct so.name
- FROM syscomments sc
- INNER JOIN sysobjects so ON sc.id = so.id
- WHERE charindex('OSAccounts', text) > 0
- EXEC sp_depends @objname = N'dbo.OSAccounts' ;
- EXEC sp_MSdependencies N'dbo.OSAccounts', null, 1315327
- --查看此对象依赖的父级对象
- EXEC sp_MSdependencies N'dbo.OSAccounts', null, 1053183
- --各表对象下的其他对象
- 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)) *
- 100 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 * from INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME=''
- SELECT * from sys.sql_modules M WHERE EXISTS(SELECT * from sys.triggers T WHERE M.object_id=T.object_id)
- 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
- SELECT * from sys.all_sql_modules
- SELECT * from sys.system_sql_modules