常用SQL SCRIPT - 1

--********************
--Get sp modify_date
--********************
select name,modify_date from sys.all_objects where type='P' order by modify_date desc
 
--********************
--Get current lock status
--********************
exec sp_lock
 <hr>

--********************
--Get object ID
--********************
print object_id('TestDB..Table_1')


--********************
--Query space allocation
--********************
select * from sys.partitions where [object_id] in(2121058592,5575058)
select * from sys.allocation_units where container_id in( 72057594038910976,72057594038976512)
select * from sys.data_spaces 
select * from sys.system_internals_allocation_units where container_id in( 72057594038910976,72057594038976512)


--********************
--Get table structure
--********************
Get table structureSELECT
表名           =   D.NAME,
字段序号       =   A.COLORDER, 
字段           =   A.NAME, 
ISIDENTITY     = CASE WHEN COLUMNPROPERTY(A.ID,A.NAME, 'ISIDENTITY ')=1 THEN '√ 'ELSE ' ' END, 
PK = 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, 
[Type]          =   B.NAME, 
[Length]        =   COLUMNPROPERTY(A.ID,A.NAME, 'PRECISION '), 
SCALE        =   ISNULL(COLUMNPROPERTY(A.ID,A.NAME, 'SCALE '),0), 
[ISNULL]        =   CASE   WHEN   A.ISNULLABLE=1   THEN   '√ 'ELSE   ' '   END, 
[Default]       =   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  
--********************
--Get FK releationship
--********************
select object_name(A.parent_obj) as 表名  
  ,col_name(A.parent_obj,B.fkey) as 列名   
  ,object_name(B.rkeyid) as 引用表名  
  ,col_name(B.rkeyid,B.rkey) as 引用列名  
from sysobjects A  
join sysforeignkeys B on A.id=B.constid where object_name(A.parent_obj) = 'trsCONT' 获取 IDENTITY--返回为任何会话和任何作用域中的"特定"表最后生成的标识值，它不受作用域和会话的限制，而受限于所指定的表。
select IDENT_CURRENT( 'table_name' )
  
--返回为当前会话的"所有"作用域中的"任何"表最后生成的标识值。
select @@IDENTITY

--返回为当前会话和"当前"作用域中的"任何"表最后生成的标识值
select SCOPE_IDENTITY   --********************
--Search text in SP
--********************select name    
from sysobjects o, syscomments s where o.id = s.id 
andtextlike'%text%'and o.xtype ='P'   代码--获取指定数据库的连接数

SELECT * FROM
[Master].[dbo].[SYSPROCESSES] WHERE [DBID]
IN
(
SELECT
   [DBID]
FROM
   [Master].[dbo].[SYSDATABASES]
WHERE
   NAME='unus2' and hostname = 'GHIBLI'
)order by hostname;