資料庫分析實用腳本

--**************************************
-- 資料庫分析實用腳本
--**************************************
--1 查詢資料庫ID                                                                                                       
select db_id('soft')
--插入預設GUID
ALTER TABLE [dbo].[DD_BandTotalProportion] ADD  CONSTRAINT [DF_DD_BandTotalProportion_Id]  DEFAULT (NEWID()) FOR [Id]

--2 查詢 連接配接資料庫的程序ID
select Hostname,spid,* from master.dbo.sysprocesses where dbid in (select dbid from master.dbo.sysdatabases where name='soft2')
and  Hostname like 'PC201710101756%'   
 
--3 查詢資料庫所有表記錄
SELECT a.name, b.rows
FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC

--4 查詢表資料字典完全版
SELECT      
  表名=case   when   a.colorder=1   then  d.name   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   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(ep.[value],'')    
  FROM  syscolumns   a    
  left  join   systypes   b   on   a.xtype=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 AS ep  ON ep.major_id = a.id and ep.class =1 and a.colorder=ep.minor_id
  where   d.name='aaClass'         --如果隻查詢指定表,加上此條件    
  order  by   a.id,a.colorder 

-- 5 查詢資料字典簡化版
SELECT 
t.[name] AS 表名,
c.[name] AS 字段名,
cast(ep.[value] as nvarchar(200)) AS [字段說明]  
FROM sys.tables AS t  
INNER JOIN sys.columns AS c ON t.object_id = c.object_id  
LEFT JOIN sys.extended_properties AS ep  ON ep.major_id = c.object_id 
AND ep.minor_id = c.column_id WHERE --ep.class =1   AND
t.name='aaClass'    

--6 查詢資料庫所有對象的定義 
--D:預設值,FN:xx函數,IF:xx函數，TF：xx函數 P：存儲過程，TR：觸發器 V ：視圖
select a.name,a.[type],b.[definition] 
from sys.all_objects a,sys.sql_modules b 
where a.is_ms_shipped=0 and a.object_id = b.object_id 
-- and a.[type] in ('P','V','AF','FN') --要查詢的對象類型
-- and a.name='對象名'    --要查詢的對象名稱
order by a.[type] asc

--1. Name：對象名
--2. Object_id：對象辨別号，在資料中是唯一的
--3. Principal_id ：架構所有者ID
--4. Parent_object_id：此對象所屬對象的ID，0 = 不是子對象
--5. Type：對象類型，常用的類型有, AF = 聚合函數 P = SQL 存儲過程  V = 視圖  TT = 表類型   U = 表（使用者定義類型）
--6. Type_desc：對象類型的說明
--7. Create_date / Modify_date ：建立日期 / 修改日期
--8. is_ms_shipped：是否為 内部 SQL Server 組建所建立的對象，常用來判斷 是否是 系統内置或使用者自定義 的對象

      

--資料庫查詢性能監控
DECLARE @t DATETIME
    SELECT @t=GETDATE()
    waitfor delay '00:00:01' --延遲1秒
    SELECT DATEDIFF(ms,@t,GETDATE())      

--判斷存儲過程是否存在
if exists(select 1 from sysobjects where id=object_id('PROC_GetQty_FromTBusRetail') and xtype='P')   
DROP PROCEDURE PROC_GetQty_FromTBusRetail      

 --如果是實表可以用

if exists (select * from sysobjects where id = object_id(N'[dbo].[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 

drop table [dbo].[表名]

--如果是臨時表可以用(說明,如果用查找實表方法來打臨時表會找不到.釋出差別對代.)

if object_id('tempdb..##temp') is not null

   drop table ##temp

資料庫鎖：幫助腳本

--鎖表（其它事務不能讀、更新、删除）
BEGIN TRAN
SELECT * FROM <表名> WITH(TABLOCKX);
WAITFOR delay '00:00:20'
COMMIT TRAN


--鎖表（其它事務隻能讀，不能更新、删除）
BEGIN TRAN
SELECT * FROM <表名> WITH(HOLDLOCK);
WAITFOR delay '00:00:20'
COMMIT TRAN

--鎖部分行
BEGIN TRAN
SELECT * FROM <表名>  WITH(XLOCK) WHERE ID IN ('81A2EDF9-D1FD-4037-A17B-1369FD3B169B');
WAITFOR delay '00:01:20'
COMMIT TRAN

--檢視被鎖表
select   request_session_id   鎖表程序,OBJECT_NAME(resource_associated_entity_id) 被鎖表名  
from   sys.dm_tran_locks where resource_type='OBJECT';

--解鎖
declare @spid  int
Set @spid  = 55 --鎖表程序
declare @sql varchar(1000)
set @sql='kill '+cast(@spid  as varchar)
exec(@sql)      

轉載于:https://www.cnblogs.com/zoumin123/p/7742492.html