SqlServer数据库常用

1、查询列名

select   name   from   syscolumns   where   id=object_id('你要查询的表名') 

 

2、查询包含某列的所有表

select name from sysobjects 

where  xtype = 'u' and '列名' in (select name from syscolumns where id=object_id(sysobjects.name))

 

3、查询所有空表
-- 声明变量
declare @count int
declare @sqls nvarchar(4000)
declare @name varchar(40)
-- 查询所有表名
declare t_cursor cursor for
select SCHEMA_NAME(schema_id) + '.[' + A.name + ']'
from sysobjects A
 inner join sys.tables  B on A.name = B.name
where A.type= 'U ' order by schema_id

open t_cursor
fetch next from t_cursor into @name
-- 遍历输出
while @@fetch_status=0
begin
 set @sqls= 'select @a=count(*) from '+@name
 exec sp_executesql @sqls,N'@a int output ',@count   output
 if(@count = 0)
 begin
  print @name
 end
    fetch   next   from   t_cursor   into   @name
end

close t_cursor
deallocate t_cursor
go

 

4、重置标识列

DBCC CHECKIDENT ('TableName',  RESEED, 0)