Set ansi_nulls off
Go
Set nocount on
Go
Set language 'simplified China'
Go
Declare @ TBL nvarchar (200), @ brief 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, policypes 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, @ override
While (@ fetch_status <>-1) --- failes
Begin
If (@ fetch_status <>-2) -- Missing
Begin
Set @ SQL = n' set @ maxlen = (select max (LEN (cast ('+ @ alias +' 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 ('+ @ alias +' As nvarchar) from '+ @ TBL +' where Len (cast ('+ @ alias +' 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' + @ resolve + '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 = @ resolve
End
Fetch next from read_cursor into @ TBL, @ override
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 ()
-- Please modify the user name and password and export it to excel
Set @ SQL = 'exec master. DBO. xp_mongoshell ''bcp... DBO. # TX OUT C:/'{@db='_exp.xls-w-c936- USA-PSA '''
Print @ SQL
Exec (@ SQL)
Go
Drop table # TX
Go