List the SQL Server all tables, field definitions, types, length, one value, etc.
And export to Excel
- ================================================================================================================================================================== ======
- Export All User Tables Definition and ONE Sample Value
- JAN-13-2003, Dr.zhang
- ================================================================================================================================================================== ======
Run in the query analyzer:
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, @seple nvarchar (40)
Select D.Name Tablename, A.Name Fieldname, B.Name Typename, A.Length Length, A.isnullable IS_NULL INTO #T
From syscolumns a, systempes 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) - MISSINGBEGIN
Set @ SQL = N'SET @ Maxlen = (SELECT MAX (LEN (Cast (' @ FLD ' AS NVARCHAR)) "" ""
--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', @seple 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', @seple 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 #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 ()
- 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
