常用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 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;