--
======================================================
-- 列出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 '+
-- @tbl+' order by 1 desc ))'
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
说明:本贴纯属收藏,目的在于大家交流,在此对作者表示感谢!
-- 列出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 '+
-- @tbl+' order by 1 desc ))'
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
说明:本贴纯属收藏,目的在于大家交流,在此对作者表示感谢!