代码
--
********************
-- 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 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 '
-- 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 structure
SELECT
表名 = 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
表名 = 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
select IDENT_CURRENT( ' table_name ' )
-- 返回为当前会话的"所有"作用域中的"任何"表最后生成的标识值。
select @@IDENTITY
-- 返回为当前会话和"当前"作用域中的"任何"表最后生成的标识值
select SCOPE_IDENTITY
--********************
--Search text in SP
--********************
select name
from sysobjects o, syscomments s
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;
SELECT * FROM
[ Master ] . [ dbo ] . [ SYSPROCESSES ] WHERE [ DBID ]
IN
(
SELECT
[ DBID ]
FROM
[ Master ] . [ dbo ] . [ SYSDATABASES ]
WHERE
NAME = ' unus2 ' and hostname = ' GHIBLI '
) order by hostname;