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

sysobjecs中对象类型(xtype)：AF = Aggregate function (CLR)C = 约束D = 默认值约束F = 外键约束L = LogFN = 标量值函数FS = Assembly (CLR) 标量值函数FT = Assembly (CLR) 表值函数IF = In-lined table-functionIT = Internal tableP = 存储过程PC = Assembly (CLR) 存储过程PK = PRIMARY KEY constraint (type is K)RF = Replication filter stored procedureS = 系统表SN = SynonymSQ = Service queueTA = Assembly (CLR) DML triggerTF = Table functionTR = SQL DML TriggerTT = Table typeU = 用户表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 csr1FETCH NEXT FROM csr1 INTO @name while (@@FETCH_STATUS=0) BEGIN SET @name='原架构名.'+@nameEXEC SP_ChangeObjectOwner @name, '新架构名' fetch next from csr1 into @nameEND 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 FREEPROCCACHEDBCC DROPCLEANBUFFERS--跨服务器的数据库查询SELECT * FROMOPENDATASOURCE('SQLOLEDB' , 'Data Source=172.18.24.245;User ID=sa;Password=aaa*'). CenterObj_xx.dbo .TableLog AS A