SQLServer 常用指令碼

--本文轉自

http://blog.csdn.net/kk185800961/article/details/8570512

--  查詢表結構   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 object_name (i.id) TableName,         rows as RowCnt   FROM sysindexes i   INNER JOIN sysObjects o       ON (o.id = i.id AND o.xType = 'U ')   WHERE indid < 2   ORDER BY TableName   --  檢視錶中的自增列是否為主鍵   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)) *     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