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    

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22996654/viewspace-2716689/,如需转载,请注明出处,否则将追究法律责任。

本文由 @bug哥[Vip] 发布于 职涯宝 ,未经作者许可,禁止转载,欢迎您分享文章

发表评论

登录后才能评论
小程序
小程序
微信客服
微信客服
QQ客服 建站服务
分享本页
返回顶部