Lists all SQL SERVER tables, field definitions, types, lengths, values, and so on, and exports to Excel
Last Update:2017-02-28
Source: Internet
Author: User
Excel|server
SET ansi_nulls off
Go
SET NOCOUNT on
Go
SET LANGUAGE ' Simplified Chinese '
Go
DECLARE @tbl nvarchar (@fld nvarchar), @sql nvarchar (4000), @maxlen int, @sample nvarchar (40)
SELECT d.name tablename,a.name fieldname,b.name typename,a.length length,a.isnullable 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 the 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 ',
' 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 (' + @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)) = ' +conv ert (nvarchar (5), @maxlen) + ') '
EXEC sp_executesql @sql, N ' @sample varchar output ', @sample output
--for quickly
--set @sql =n ' SET @sample =convert (varchar), (SELECT top 1 ' + @fld + ' from ' +
--@tbl + ' ORDER by 1 desc) '
PRINT @sql
Print @sample
Print @tbl
EXEC sp_executesql @sql, N ' @sample nvarchar output ', @sample output
INSERT into #tc SELECT *,ltrim (ISNULL (@maxlen, 0)) as maxlenused,
CONVERT (nchar), 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 to #tc ORDER by TableName
DROP TABLE #tc
--select * FROM # #tx
Declare @db nvarchar, @sql nvarchar (3000)
Set @db =db_name ()
--Please modify the username and password to export to 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