SQL查询

1.查询系统是否存在列的所有表名

SELECT b.name as TableName,a.name as columnname
From syscolumns a INNER JOIN sysobjects b
ON a.id=b.id
AND b.type=’U’
AND a.name=’你的字段名字’`

2. 使用游标查询表中是否存在指定数据，存在就保存

 ALTER  proc [dbo].[global_search] 
    @strValue nvarchar(MAX)--search value
as 
    declare @tab_name nvarchar(300),@col_name sysname,@sql nvarchar(1000) 
    begin
        declare curTab_cursor cursor fast_forward 
        for 
            select '['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c inner join sys.objects o on c.object_id=o.object_id 
            where o.type_desc='user_table' and c.user_type_id in(56,167,175,231,239)
			AND  (O.name NOT like 'A_%' AND  o.name NOT like 'Z_%' )
			AND (C.name NOT IN ('CREATEMAN','MODIFYMAN'))
            --user_type_id Only check int varchar char nvarchar nchar five types, if you need can be extended 
    end
    open curTab_cursor 
    fetch next from curTab_cursor into @tab_name,@col_name 
    while @@FETCH_STATUS=0 
    begin
        set @sql='if exists (select * from '+@tab_name+' where ' 
        begin
			set @sql += @col_name + ' like ''%'+@strValue +'%'')'  --模糊查询
			--set @sql += @col_name + ' = '''+@strValue +''')' 
        end
        set @sql += ' INSERT A_search_328 ([tab_name],[col_name]) VALUES('''+@tab_name+''','''+@col_name+''')'
        --print @sql 
        exec (@sql) 
        fetch next from curTab_cursor into @tab_name,@col_name 
    end
    close curTab_cursor  
    deallocate curTab_cursor

3.循环删除多个表

DECLARE  @varname Nvarchar(128)
WHILE  EXISTS(SELECT name from SYS.tables where name like 'Z_BAK%')
BEGIN
SELECT TOP 1 @varname=name from sys.tables where name like N'Z_BAK%'
EXEC ('DROP TABLE '+ @varname)
END

4.使用循环加游标修改数据库的数据

/*  
 凌晨审核生产单据全部数据
*/
--外层循环使用变量
DECLARE @middle TABLE(
TABLENAME VARCHAR(MAX)
)
INSERT INTO @middle(TABLENAME)
SELECT DISTINCT OBJ.NAME AS TABLENAME --,COL.name
FROM SYSCOLUMNS COL INNER JOIN sysobjects OBJ
ON COL.id=OBJ.id AND OBJ.type='U' 
AND COL.name='列名'
DECLARE  @TABLENAME AS NVARCHAR(MAX)
WHILE EXISTS(SELECT TABLENAME FROM @middle)
BEGIN
SELECT @TABLENAME= TABLENAME FROM @middle
BEGIN TRAN
--内层游标使用变量
DECLARE @EXECSQL NVARCHAR(MAX)  --需要执行的SQL
DECLARE @FID varchar(50)  
DECLARE @Error int
SET @Error=0
SET @EXECSQL=' DECLARE common_cursor CURSOR FOR (  SELECT FID FROM  '+@TABLENAME+'  )';
EXEC(@EXECSQL);
OPEN common_cursor
FETCH NEXT FROM  common_cursor INTO @FID
WHILE @@FETCH_STATUS=0 
BEGIN

SET @EXECSQL='  UPDATE '+@TABLENAME+' SET  列名=1,列名2='''',列名3=GETDATE() WHERE FID='''+@FID+''' ';
EXEC(@EXECSQL);
SET @Error=@Error+@@ERROR
FETCH NEXT FROM common_cursor INTO @FID   --转到下一个游标
END
IF(@Error=0)
BEGIN
PRINT('提交事务')
COMMIT TRAN --提交事务
END
ELSE
BEGIN
PRINT('回滚事务')
ROLLBACK TRAN --回滚事务
END
CLOSE common_cursor --关闭游标
PRINT('关闭游标')
DEALLOCATE  common_cursor --释放游标
SELECT @TABLENAME
DELETE FROM @middle WHERE TABLENAME=@TABLENAME
END

---

批量遍历数据库数据，与其特定数据比对

---

DECLARE  @ANIMAL AS NVARCHAR(MAX)   --每一列的数据
WHILE EXISTS(SELECT OLDANIMAL FROM #middleTable)
BEGIN
SELECT @ANIMAL= OLDANIMAL FROM #middleTable

  declare @tab_name nvarchar(300),@col_name sysname,@sql nvarchar(1000) 
    begin
        declare curTab_cursor cursor fast_forward 
        for 
           select '['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c inner join sys.objects o on c.object_id=o.object_id 
            where o.type_desc='user_table' and c.user_type_id in(167,175,231,239)  --56 int 类型
			AND  (O.name NOT like 'A_%' AND  o.name NOT like 'Z_%' AND o.name NOT LIKE 'CODE%' AND O.name NOT LIKE 'SYS%'AND O.name NOT LIKE '%_M' )
			AND (O.name NOT IN ('SYS_USER_ROLE','SYS_USER','SYS_USER_ADMIN','SYS_AUTO_EVENTS','SP_TZLINK_BATCH','SYS_AUTO_SET','SP_TZLINK_BREED','CBENMU','SYS_FINANCE_DAYS','SYS_USER_RIGHT_BASE','SYS_INFO_CLIENT'))
			AND (C.name NOT IN (	select name from syscolumns where id=object_id('SP_FARMMAT_M') and name NOT in ('USERDEFID') ))
		    AND (C.name NOT IN ('PEN','EMPLOYEEID','REASON','BATCHIDS','SWINTYPEID','EVENTID','INDATE','MAINID','UPDATETIME','NOTE','CODENAME'))      
            --user_type_id Only check int varchar char nvarchar nchar five types, if you need can be extended 
    end
	    open curTab_cursor 
    fetch next from curTab_cursor into @tab_name,@col_name 
    while @@FETCH_STATUS=0 
    begin
        set @sql='if exists (select top 1 * from '+@tab_name+' where ' 
        begin
			set @sql += @col_name + ' like ''%'+@ANIMAL +'%'')'  --模糊查询
        end
        set @sql += ' INSERT A_search_328 ([tab_name],[col_name],ANIMAL) VALUES('''+@tab_name+''','''+@col_name+''','''+@ANIMAL+''')'
        --print @sql 
        exec (@sql) 
        fetch next from curTab_cursor into @tab_name,@col_name 
    end
    close curTab_cursor  
    deallocate curTab_cursor
--SELECT @ANIMAL
DELETE FROM #middleTable WHERE OLDANIMAL=@ANIMAL
END

SQL 跨服务器查询

--exec sp_addlinkedserver 'SERVER', ' ', 'SQLOLEDB ', 'IP'
--exec sp_addlinkedsrvlogin 'SERVER', 'false ',null, '账号', '密码'