收藏几段SQL Server语句和存储过程
-- ======================================================
--列出SQL SERVER 所有表，字段名，主键，类型，长度，小数位数等信息
--在查询分析器里运行即可,可以生成一个表，导出到EXCEL中
-- ======================================================
SELECT
(case when a.colorder=1 then d.name else '' end)表名,
a.colorder 字段序号,
a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) 主键,
b.name 类型,
a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
(case when a.isnullable=1 then '√'else '' end) 允许空,
isnull(e.text,'') 默认值,
isnull(g.[value],'') AS 字段说明
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by a.id,a.colorder
-------------------------------------------------------------------------------------------------
列出SQL SERVER 所有表、字段定义，类型，长度，一个值等信息
并导出到Excel 中
-- ======================================================
-- Export all user tables definition and one sample value
-- jan-13-2003,Dr.Zhang
-- ======================================================
在查询分析器里运行：
SET ANSI_NULLS OFF
GO
SET NOCOUNT ON
GO
SET LANGUAGE 'Simplified Chinese'
go
DECLARE @tbl nvarchar(200),@fld nvarchar(200),@sql nvarchar(4000),@maxlen int,@sample nvarchar(40)
SELECT d.name TableName,a.name FieldName,b.name TypeName,a.length Length,a.isnullable IS_NULL INTO #t
FROM syscolumns a, systypes b,sysobjects d
WHERE a.xtype=b.xusertype and a.id=d.id and d.xtype='U'
DECLARE read_cursor CURSOR
FOR SELECT TableName,FieldName FROM #t
SELECT TOP 1 '_TableName ' TableName,
'FieldName ' FieldName,'TypeName ' TypeName,
'Length' Length,'IS_NULL' IS_NULL,
'MaxLenUsed' AS MaxLenUsed,'Sample Value ' Sample,
'Comment ' Comment INTO #tc FROM #t
OPEN read_cursor
FETCH NEXT FROM read_cursor INTO @tbl,@fld
WHILE (@@fetch_status <> -1) --- failes
BEGIN
IF (@@fetch_status <> -2) -- Missing
BEGIN
SET @sql=N'SET @maxlen=(SELECT max(len(cast('+@fld+' as nvarchar))) FROM '+@tbl+')'
--PRINT @sql
EXEC SP_EXECUTESQL @sql,N'@maxlen int OUTPUT',@maxlen OUTPUT
--print @maxlen
SET @sql=N'SET @sample=(SELECT TOP 1 cast('+@fld+' as nvarchar) FROM '+@tbl+' WHERE len(cast('+@fld+' as nvarchar))='+convert(nvarchar(5),@maxlen)+')'
EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUT
--for quickly
--SET @sql=N'SET @sample=convert(varchar(20),(SELECT TOP 1 '+@fld+' FROM '+
PRINT @sql
print @sample
print @tbl
EXEC SP_EXECUTESQL @sql,N'@sample nvarchar(30) OUTPUT',@sample OUTPUT
INSERT INTO #tc SELECT *,ltrim(ISNULL(@maxlen,0)) as MaxLenUsed,
convert(nchar(20),ltrim(ISNULL(@sample,' '))) as Sample,' ' Comment FROM #t where TableName=@tbl and FieldName=@fld
END
FETCH NEXT FROM read_cursor INTO @tbl,@fld
END
CLOSE read_cursor
DEALLOCATE read_cursor
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT OFF
GO
select count(*) from #t
DROP TABLE #t
GO
select count(*)-1 from #tc
select * into ##tx from #tc order by tablename
DROP TABLE #tc
--select * from ##tx
declare @db nvarchar(60),@sql nvarchar(3000)
set @db=db_name()
--请修改用户名和口令 导出到Excel 中
set @sql='exec master.dbo.xp_cmdshell ''bcp ..dbo.##tx out c:\'+@db+'_exp.xls -w -C936 -Usa -Psa '''
print @sql
exec(@sql)
GO
DROP TABLE ##tx
GO
-- ======================================================
--根据表中数据生成insert语句的存储过程
--建立存储过程，执行 spGenInsertSQL 表名
--感谢playyuer
-- ======================================================
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
-- ======================================================
--根据表中数据生成insert语句的存储过程
--建立存储过程，执行 proc_insert 表名
--感谢Sky_blue
-- ======================================================
CREATE proc proc_insert (@tablename varchar(256))
as
begin
set nocount on
declare @sqlstr varchar(4000)
declare @sqlstr1 varchar(4000)
declare @sqlstr2 varchar(4000)
select @sqlstr='select ''insert '+@tablename
select @sqlstr1=''
select @sqlstr2=' ('
select @sqlstr1= ' values ( ''+'
select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case
-- when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'
when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =108 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'
-- when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
else '''NULL'''
end as col,a.colid,a.name
from syscolumns a where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36
)t order by colid
select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename
-- print @sqlstr
exec( @sqlstr)
set nocount off
end
GO
-- 1. 属性
属性名 类型 描述
--------------------------------- ------------------- --------------------
CopyAllDefaults Boolean 所有默认值
CopyAllObjects Boolean 所有对象
CopyAllRules Boolean 所有规则
CopyAllStoredProcedures Boolean 所有存储过程
CopyAllTables Boolean 所有表
CopyAllTriggers Boolean 所有触发器
CopyAllUserDefinedDatatypes Boolean 所有用户自定义类型
CopyAllViews Boolean 所有视图
CopyData Boolean 所有数据
DestDatabase String 目标对象数据库
DestLogin String 目标数据库登陆用户名
DestPassword String 目标数据库登陆密码
DestServer String 目标服务器
DestUseTrustedConnection Boolean 用户信任连接
DropDestObjectsFirst Boolean 是否先删除目标对象
IncludeDependencies Boolean 是否包含依靠对象
ScriptType Boolean 脚本类型
-- 2. 重要方法:
方法名称 功能描述
--------------------------- --------------------------
AddObject 增加对象
AddObjectByName 通过对象名称增加对象
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_CopyDB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[P_CopyDB]
GO
/*-- 在 SQLServer 中使用SQLDMO.Transfer 实现数据迁移
存储过程实现源数据库到目标数据库的对象和数据的复制
要求源数据库和目标数据库在同一服务器
如果是要实现不同服务器之间的复制，则需要增加验证信息
--邹建 2005.07(引用请保留此信息)--*/
/*--调用示例
CREATE DATABASE test
EXEC P_CopyDB @Source_DB='northwind',@Des_DB='test'
DROP DATABASE test
--*/
CREATE PROCEDURE P_CopyDB
@Des_DB sysname, --目标数据库
@Obj_Type nvarchar(4000)=N'',--复制的对象类型，可以是下列字符串列表：
-- O 所有对象，D 默认值，R 规则，P 存储过程
-- T 表，TR 触发器，DT 用户定义数据类型
-- V 视图，DATA 数据，DEL 删除目标对象
@Source_DB sysname=N'', --源数据库
@ServerName sysname=N'', --服务器名
@UserName sysname=N'', --用户名，不指定则表示使用 Windows 身份登录
@pwd sysname=N'' --密码
AS
SET NOCOUNT ON
DECLARE @srvid int,@Dbid int,@S_dbid int,@D_dbid int,@TransferID int,
@err int,@src varchar(255), @desc varchar(255)
IF ISNULL(@ServerName,N'')=N'' SET @ServerName=@@SERVERNAME
IF ISNULL(@Source_DB,N'')=N'' SET @Source_DB=DB_NAME()
--创建sqldmo对象·
EXEC @err=sp_oacreate 'sqldmo.sqlserver',@srvid OUT
IF @err<>0 GOTO lb_Err
--连接服务器
IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录
BEGIN
EXEC @err=sp_oasetproperty @srvid,'loginsecure',-1
IF @err<>0 GOTO lb_Err
EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername
END
ELSE
EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername,@UserName,@pwd
IF @err<>0 GOTO lb_Err
--获取数据库集
EXEC @err=sp_oagetproperty @srvid,'databases',@Dbid OUT
IF @err<>0 GOTO lb_Err
--选择源数据库
EXEC @err=sp_oamethod @Dbid,'item',@S_dbid OUT,@Source_DB
IF @err<>0 GOTO lb_Err
--选择目标数据库
EXEC @err=sp_oamethod @Dbid,'item',@D_dbid OUT,@Des_DB
IF @err<>0 GOTO lb_Err
--设置复制的对象
EXEC @err=sp_oacreate 'SQLDMO.Transfer',@TransferID OUT
IF @err<>0 GOTO lb_Err
--设置目标服务器信息
EXEC @err=sp_oasetproperty @TransferID,'DestServer',@ServerName
IF @err<>0 GOTO lb_Err
--设置连接用户
IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录
BEGIN
EXEC @err=sp_oasetproperty @TransferID,'DestUseTrustedConnection',1
IF @err<>0 GOTO lb_Err
END
ELSE
BEGIN
EXEC @err=sp_oasetproperty @TransferID,'DestLogin',@UserName
IF @err<>0 GOTO lb_Err
EXEC @err=sp_oasetproperty @TransferID,'DestPassword',@pwd
IF @err<>0 GOTO lb_Err
END
--设置复制对象信息
EXEC @err=sp_oasetproperty @TransferID,'DestDatabase',@Des_DB
IF @err<>0 GOTO lb_Err
DECLARE tb CURSOR FAST_FORWARD LOCAL
FOR
SELECT Name FROM(
SELECT KeyWord=N',D,', Name=N'CopyAllDefaults' UNION ALL
SELECT KeyWord=N',O,', Name=N'CopyAllObjects' UNION ALL
SELECT KeyWord=N',R,', Name=N'CopyAllRules' UNION ALL
SELECT KeyWord=N',P,', Name=N'CopyAllStoredProcedures' UNION ALL
SELECT KeyWord=N',T,', Name=N'CopyAllTables' UNION ALL
SELECT KeyWord=N',TR,', Name=N'CopyAllTriggers' UNION ALL
SELECT KeyWord=N',DT,', Name=N'CopyAllUserDefinedDatatypes' UNION ALL
SELECT KeyWord=N',V,', Name=N'CopyAllViews' UNION ALL
SELECT KeyWord=N',DATA,',Name=N'CopyData' UNION ALL
SELECT KeyWord=N',DEL,', Name=N'DropDestObjectsFirst'
)A WHERE CHARINDEX(KeyWord,
CASE WHEN ISNULL(@Obj_Type,N'')='' THEN ',O,DATA,' ELSE @Obj_Type END)>0
OPEN tb
FETCH tb INTO @src
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @err=sp_oasetproperty @TransferID,@src,1
IF @err<>0 GOTO lb_Err
FETCH tb INTO @src
END
CLOSE tb
DEALLOCATE tb
--复制对象
EXEC @err=sp_oamethod @S_dbid,'Transfer',null,@TransferID
IF @err<>0 GOTO lb_Err
--结束
SET @err=0
GOTO lb_Exit
--错误处理
lb_Err:
EXEC sp_oageterrorinfo NULL, @src OUT, @desc OUT
RAISERROR(N'错误编号 %#x, 错误源 "%s", 错误描述 "%s"',16,1,@err,@src,@desc)
RETURN -1
lb_Exit:
EXEC sp_OADestroy @Dbid
EXEC sp_OADestroy @srvid
EXEC sp_OADestroy @TransferID
RETURN @err
GO
