常用sql集锦

1、从数据库A中把表tableA导入到数据库B中

　

--如果主键是自增，则必须列出具体字段.--　
select * into tableA from A..tableA    　　

2、批量更改表中某列中的某个字符串

　

　update table[表名] set Fields[字段名]=replace(Fields[字段名],'被替换原内容','要替换成的内容')

3、把数据库中某张表数据生成插入语句

CREATE   proc spGenInsertSQL (@tablename varchar(256))
as 
begin 
declare @sql varchar(8000)
declare @sqlValues varchar(8000)
set @sql =' ('
set @sqlValues = 'values (''+'select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'
from     
 (select case when xtype in (48,52,56,59,60,62,104,106,108,122,127)   
     then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end' 
     when xtype in (58,61) then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end' 
              when xtype in (167)                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'    
            when xtype in (231)                     then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'           
     when xtype in (175)                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'      
          when xtype in (239)                     then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'          
      else '''NULL'''              end as Cols,name         from syscolumns         where id = object_id(@tablename)      ) T
set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
print @sql
exec (@sql)
end 
GO

4、分页存储过程

drop procedure Common_Page
Create PROCEDURE Common_Page
(
@tablename nvarchar(255),--操作的表名
@pagesize int,--单页显示个数
@pageindex int,--当前页码
@condition nvarchar(1000),--筛选条件,格式: a=1 and b=2
@primarykey nvarchar(255),--主键,格式: id
@order nvarchar(255)--排序,格式: id desc,id2 desc
)
AS

DECLARE @sqlcount nvarchar(2000)
DECLARE @sql nvarchar(2000)
DECLARE @orderby nvarchar(255)

SET @orderby = ISNULL(@order,@primarykey)
IF(LEN(@orderby)=0)
 SET @orderby=@primarykey

SET @orderby=' '+@orderby
SET @sql='WITH temptbl AS ('
SET @sql=@sql+'Select ROW_NUMBER() OVER (ORDER BY '+@orderby+')AS ROW_NUM, * from '+@tablename

set @sqlcount='Select count(0) FROM '+@tablename
IF(LEN(ISNULL(@condition,''))>0)
BEGIN
 set @sqlcount=@sqlcount+ ' where ' + @condition
 set @sql=@sql+ ' where ' + @condition
End

DEclARE @rowstart nvarchar(20),@rowend nvarchar(20)
if(@pageindex<0) SET @pageindex=0
SET @rowstart=(@pageindex)*@pagesize+1
SET @rowend=CAST((@pageindex)*@pagesize+@pagesize as nvarchar(20))

-- 此处会导致第三页开始显示不正确的问题,故注释掉,复制时要把这些删掉
-- update:2008-6-8
--if(@pageindex>2)
--BEGIN
-- SET @rowstart=CAST((@pageindex*@pagesize)+1 as nvarchar(20))
-- SET @rowend=CAST((@pageindex*@pagesize)+@pagesize as nvarchar(20))
--END

set @sql=@sql+')Select * FROM temptbl where ROW_NUM between '+@rowstart+' and '+@rowend

EXECUTE sp_executesql @sql
EXECUTE sp_executesql @sqlcount
print(@sql)
GO

 5、使用脚本断开某个数据库的所有活动链接

　　

USE master
go

IF EXISTS ( SELECT  *
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[P_KillConnections]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) 
    DROP PROCEDURE [dbo].[P_KillConnections]
GO

CREATE PROC P_KillConnections @dbname VARCHAR(200)
AS 
    DECLARE @sql NVARCHAR(500)
    DECLARE @spid NVARCHAR(20)

    DECLARE #tb CURSOR FOR
    SELECT spid=CAST(spid AS VARCHAR(20)) FROM master..sysprocesses WHERE dbid=DB_ID(@dbname)
    OPEN #tb
    FETCH NEXT FROM #tb INTO @spid
    WHILE @@fetch_status = 0 
        BEGIN
            EXEC('kill '+@spid)
            FETCH NEXT FROM #tb INTO @spid
        END
    CLOSE #tb
    DEALLOCATE #tb
go

EXEC P_KillConnections '修改成自己的数据库'

 6、删除登录用户失败时，执行下面脚本，然后再删除

ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;

 7、数据库分离与附加

　　

--分离--
USE master;
GO
EXEC sp_detach_db @dbname = N'数据库名称';
GO

--附加--
USE master;
GO
if exists(select * from dbo.sysdatabases where name='数据库名称')  
   drop database 数据库名称
GO
CREATE DATABASE 数据库名称
    ON (FILENAME = 'E:DB数据库名称.mdf'),
    (FILENAME = 'E:DB数据库名称.ldf')
    FOR ATTACH;
GO

 8、数据库备份与还原

　　

--数据库备份--
USE master 
BACKUP DATABASE 数据库名称 
TO DISK ='E:DB数据库名称.bak' 


--数据库还原--
USE master 
RESTORE DATABASE 数据库名称 
FROM DISK='E:DB数据库名称.bak' 
WITH REPLACE