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

我不是女神ヾ 2022-07-15 05:11 213阅读 0赞

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