SQL SERVER 快捷脚本（持续更新）

一键删除

//一键删除
方便删除数据库中所有的数据表，清空数据库，有些有约束，不能直接delete，需要先删除库中的约束，代码如下：

--删除所有约束
DECLARE c1 cursor for
    select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
    from sysobjects
    where xtype = 'F'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
    begin
        exec(@c1)
        fetch next from c1 into @c1
    end
close c1
deallocate c1

--删除数据库所有表
declare @tname varchar(8000)
set @tname=''
select @tname=@tname + Name + ',' from sysobjects where xtype='U'
select @tname='drop table ' + left(@tname,len(@tname)-1)
exec(@tname)

如果需要删除存储过程等只需要将上面的做如下修改就行了的where xtype='U' 改成 where xtype='P'，drop table 改成 drop Procedure

sysobjects的xtype代表含义:

在数据库内创建的每个对象（约束、默认值、日志、规则、存储过程等）在表中占一行。只有在 tempdb 内，每个临时对象才在该表中占一行。 

列名 数据类型 描述 
name sysname 对象名。 
Id int 对象标识号。 
xtype char(2) 对象类型。可以是下列对象类型中的一种： 
C = CHECK 约束 
D = 默认值或 DEFAULT 约束 
F = FOREIGN KEY 约束 
L = 日志 
FN = 标量函数 
IF = 内嵌表函数 
P = 存储过程 
PK = PRIMARY KEY 约束（类型是 K） 
RF = 复制筛选存储过程 
S = 系统表 
TF = 表函数 
TR = 触发器 
U = 用户表 
UQ = UNIQUE 约束（类型是 K） 
V = 视图 
X = 扩展存储过程 

uid smallint 所有者对象的用户 ID。 
info smallint 保留。仅限内部使用。 
status int 保留。仅限内部使用。 
base_schema_ 
ver int 保留。仅限内部使用。 
replinfo int 保留。供复制使用。 
parent_obj int 父对象的对象标识号（例如，对于触发器或约束，该标识号为表 ID）。 
crdate datetime 对象的创建日期。 
ftcatid smallint 为全文索引注册的所有用户表的全文目录标识符，对于没有注册的所有用户表则为 0。 
schema_ver int 版本号，该版本号在每次表的架构更改时都增加。 
stats_schema_ 
ver int 保留。仅限内部使用。 
type char(2) 对象类型。可以是下列值之一： 
C = CHECK 约束 
D = 默认值或 DEFAULT 约束 
F = FOREIGN KEY 约束 
FN = 标量函数 
IF = 内嵌表函数 
K = PRIMARY KEY 或 UNIQUE 约束 
L = 日志 
P = 存储过程 
R = 规则 
RF = 复制筛选存储过程 
S = 系统表 
TF = 表函数 
TR = 触发器 
U = 用户表 
V = 视图 
X = 扩展存储过程 
userstat smallint 保留。 
sysstat smallint 内部状态信息。 
indexdel smallint 保留。 
refdate datetime 留作以后使用。 
version int 留作以后使用。 
deltrig int 保留。 
instrig int 保留。 
updtrig int 保留。 
seltrig int 保留。 
category int 用于发布、约束和标识。 
cache smallint 保留。  

查询表的索引及约束

//查询表的索引及约束
--查看表的索引
SELECT CASE
           WHEN t.[type] = 'U' THEN
               '表'
           WHEN t.[type] = 'V' THEN
               '视图'
       END AS '类型',
       SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS '(表/视图)名称',
       i.[name] AS 索引名称,
       SUBSTRING(column_names, 1, LEN(column_names) - 1) AS '列名',
       CASE
           WHEN i.[type] = 1 THEN
               '聚集索引'
           WHEN i.[type] = 2 THEN
               '非聚集索引'
           WHEN i.[type] = 3 THEN
               'XML索引'
           WHEN i.[type] = 4 THEN
               '空间索引'
           WHEN i.[type] = 5 THEN
               '聚簇列存储索引'
           WHEN i.[type] = 6 THEN
               '非聚集列存储索引'
           WHEN i.[type] = 7 THEN
               '非聚集哈希索引'
       END AS '索引类型',
       CASE
           WHEN i.is_unique = 1 THEN
               '唯一'
           ELSE
               '不唯一'
       END AS '索引是否唯一'
FROM sys.objects t
    INNER JOIN sys.indexes i
        ON t.object_id = i.object_id
    CROSS APPLY
(
    SELECT col.[name] + ', '
    FROM sys.index_columns ic
        INNER JOIN sys.columns col
            ON ic.object_id = col.object_id
               AND ic.column_id = col.column_id
    WHERE ic.object_id = t.object_id
          AND ic.index_id = i.index_id
    ORDER BY col.column_id
    FOR XML PATH('')
) D(column_names)
WHERE t.is_ms_shipped <> 1
      AND index_id > 0 and t.name in ('TABLE A')
ORDER BY i.[name];

--外键约束（通过本表查外键表-本表是主表时）
select 
  oSub.name  AS  [子表名称],
  fk.name AS  [外键名称],
  SubCol.name AS [子表列名],
  oMain.name  AS  [主表名称],
  MainCol.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 and oMain.name in ('TABLE A'))
    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 T1.name[约束名],T3.name[外键表],T4.name[外键列名]
    ,T5.name[主键表],T6.name[主键列名]
FROM SYS.FOREIGN_KEYS T1
    JOIN SYS.FOREIGN_KEY_COLUMNS T2 ON T1.parent_object_id=T2.parent_object_id
    JOIN SYS.SYSOBJECTS T3 ON T1.parent_object_id=T3.id
    JOIN SYS.SYSCOLUMNS T4 ON T2.parent_object_id=T4.id AND T2.parent_column_id=T4.colid
    JOIN SYS.SYSOBJECTS T5 ON T1.referenced_object_id=T5.id
    JOIN SYS.SYSCOLUMNS T6 ON T2.referenced_object_id=T6.id AND T2.referenced_column_id=T6.colid
WHERE T1.parent_object_id=OBJECT_ID('TABLE A')

--主键约束
SELECT
  tab.name AS [表名],
  idx.name AS [主键名称],
  col.name AS [主键列名]
FROM
  sys.indexes idx
    JOIN sys.index_columns idxCol 
      ON (idx.object_id = idxCol.object_id 
          AND idx.index_id = idxCol.index_id 
          AND idx.is_primary_key = 1)
    JOIN sys.tables tab
      ON (idx.object_id = tab.object_id and tab.name='TABLE A')
    JOIN sys.columns col
      ON (idx.object_id = col.object_id
          AND idxCol.column_id = col.column_id);

--Check约束
SELECT
  tab.name AS [表名],
  chk.name AS [Check约束名],
  col.name AS [列名],
  chk.definition
FROM
  sys.check_constraints chk
    JOIN sys.tables tab
      ON (chk.parent_object_id = tab.object_id and tab.name in ('TABLE A'))
    JOIN sys.columns col
      ON (chk.parent_object_id = col.object_id
          AND chk.parent_column_id = col.column_id)

--获取表名及表的触发器
select 
 (select b.name from sysobjects as b where b.id = a.parent_obj)  表名,
   a.name  as 触发器 
   from sysobjects  as a where a.xtype='TR' and a.name in ('TABLE A')
order by 表名

--唯一约束
SELECT
  tab.name AS [表名],
  idx.name AS [约束名称],
  col.name AS [约束列名]
FROM
  sys.indexes idx
    JOIN sys.index_columns idxCol 
      ON (idx.object_id = idxCol.object_id 
          AND idx.index_id = idxCol.index_id 
          AND idx.is_unique_constraint = 1)
    JOIN sys.tables tab
      ON (idx.object_id = tab.object_id and tab.name in ('TABLE A'))
    JOIN sys.columns col
      ON (idx.object_id = col.object_id
          AND idxCol.column_id = col.column_id);

解除、还原约束

//解除、还原约束
--此时需要让其禁用TbaleB与之对应的约束

--需要执行

ALTER TABLE dbo.TbaleB NOCHECK CONSTRAINT ALL

--此时TbaleB与TableA不再检查约束

--然后再

DELETE  dbo.TableA

--最后需要还原约束

ALTER TABLE dbo.TbaleB CHECK CONSTRAINT ALL

查询某库里包含的表名和表行数

//查询某库里包含的表名和表行数
select  a.name as '表名',b.rows as '表数据行数'
from sysobjects a inner join sysindexes b
on a.id = b.id
where   a.type = 'u'
and b.indid in (0,1)
--and a.name not like 't%'
order by b.rows desc

查询TABLE A在哪些库中

// 查询TABLE A在哪些库中
use master --切换到系统数据库,因为下面用到的  系统存储过程和系统表  大部分存在于该数据库
go
------------------在当前服务器  根据表的名字  在多个数据库进行查询  得到哪个数据库存在该表的信息------------------
declare @DataBaseName nvarchar(max) --定义变量(数据库的名字)
declare cur cursor for select [name] from [sysdatabases] --定义游标,该游标指向  当前  服务器上的所有数据库名字列表
open cur --打开游标
create table #TableInfo (table_catalog nvarchar(max),table_schema nvarchar(max),table_name nvarchar(max),table_type nvarchar(max)) --创建临时表用于存储所有数据库的所有表信息
fetch next from cur into @DataBaseName --获取游标的数据,相当于获取数据库名字列表的第一条数据
while (@@fetch_status=0)
begin
print ''
print ''
print '当前数据库: '+ @DataBaseName --读出每个数据库的名字
insert into #TableInfo --把存储过程查询出来的数据插进临时表
exec('select table_catalog,table_schema,table_name,table_type from ' + @DataBaseName + '.information_schema.tables') --查询对应数据库的所有表
print '--------------------------------------------------------------------------------------------------------------------------------------'
fetch next from cur into @DataBaseName --游标移动
end
close cur --关闭游标
deallocate cur --释放游标
print ''
print ''
print ''
print ''
print ''
declare @TableName nvarchar(max)
set @TableName = 'TABLE A' --查询条件(根据需要自行修改)
if exists(select table_name from #TableInfo where table_name = @TableName) --查询指定名字的表
begin
print '====================当前服务器存在 ' + @TableName + ' 表,相关信息请到结果窗口查看===================='
select table_catalog as '所属数据库',table_name as '表名' from #TableInfo where table_name = @TableName --输出表的相关信息,从这些信息就可以知道这个表在哪个数据库
end
else
begin
print '--------------------当前服务器不存在 ' + @TableName + ' 表--------------------'
end
drop table #TableInfo --删除临时表

查询库中表信息

select name from sysobjects where xtype=‘u‘ --读取库中的所有表名

select name from syscolumns where id=(select max(id) from sysobjects where xtype=‘u‘ and name=‘表名‘) --读取指定表的所有列名

select * from sysobjects where xtype=‘u‘ AND EXISTS(select 1 from syscolumns where id=sysobjects.id AND name=‘Attachment‘) --查询包含Attachment字段的表