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) - MISSING
Begin
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
