SqlServer常用语句整理（后续更新）

sysobjecs中对象类型(xtype)：
AF = Aggregate function (CLR)
C = 约束
D = 默认值约束
F = 外键约束
L = Log
FN = 标量值函数
FS = Assembly (CLR) 标量值函数
FT = Assembly (CLR) 表值函数
IF = In-lined table-function
IT = Internal table
P = 存储过程
PC = Assembly (CLR) 存储过程
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = 系统表
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = Table function
TR = SQL DML Trigger
TT = Table type
U = 用户表
UQ = UNIQUE constraint (type is K)
V = 试图
X = 扩展存储过程

--批量修改表名为小写
declare @sql varchar(300)--,@rowcount varchar(10),@dyncnum int 
     declare @tablename varchar(100) 
     declare cursor1 cursor for         
     select name  from sysobjects  where xtype = 'u'  order by name               
     open cursor1                        
     fetch next from cursor1 into @tablename 
     while @@fetch_status=0            
     begin
         set @sql='sp_rename '''+@tablename+''','''+lower(@tablename)+'''' -- 此为修改为小写，如果修改为大写“upper”
         exec(@sql)              
         fetch next from cursor1 into @tablename 
     end
     close cursor1                    
     deallocate cursor1


--批量修改字段名为小写
declare @sql varchar(300)
     declare @tablecolumnname varchar(100), @columnname varchar(100)
     declare cursor1 cursor for         
     select b.name+'.['+a.name+']',a.name from syscolumns a  ,sysobjects b where a.id = object_id(b.name) and b.xtype = 'u' and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36                
     open cursor1                       
     fetch next from cursor1 into @tablecolumnname,@columnname
     while @@fetch_status=0           
     begin
         set @sql='sp_rename '''+@tablecolumnname+''','''+lower(@columnname)+''',''column''' -- 此为修改为小写，如果修改为大写“upper”
         exec(@sql)             
         fetch next from cursor1 into @tablecolumnname,@columnname
     end
     close cursor1                   
     deallocate cursor1

--批量修改架构名（包括表名和存储过程名）
declare @name sysname 
declare csr1 cursor 
for select TABLE_NAME from INFORMATION_SCHEMA.TABLES 
open csr1
FETCH NEXT FROM csr1 INTO @name 
while (@@FETCH_STATUS=0) 
BEGIN 
SET @name='原架构名.'+@name
EXEC SP_ChangeObjectOwner @name, '新架构名' 
fetch next from csr1 into @name
END 
CLOSE csr1 
DEALLOCATE csr1 

--快速查询表的总记录数
SELECT rows FROM sysindexes WHERE id= OBJECT_ID('rpt2014' ) AND indid< 2


--非递归查询树形结构表的所有子节点
WITH Tree AS (
       SELECT * FROM dbo .MgrObjType WHERE Id='00000000-A001-0000-0000-000000000000'
       UNION ALL
       SELECT MgrObjType.* FROM dbo .MgrObjType, Tree WHERE Tree.Id= dbo.MgrObjType .ParentId
)
SELECT * FROM Tree



--清除查询缓存

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS


--跨服务器的数据库查询
SELECT * FROM
OPENDATASOURCE('SQLOLEDB' , 'Data Source=172.18.24.245;User ID=sa;Password=aaa*'). CenterObj_xx.dbo .TableLog AS A