Lists all SQL SERVER tables, field definitions, types, lengths, values, and so on, and exports to Excel

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