SET NOCOUNT ON

GO

SET LANGUAGE ''''Simplified Chinese''''

go

DECLARE @tbl nvarchar(200),@fld 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,  systypes 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))) 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))=''''+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 ''''+@fld+'''' FROM ''''+

                     --@tbl+'''' order

上一页  [1] [2] [3] [4] [5] [6] [7] [8]  下一页