列出SQL SERVER 所有表、字段定义，类型，长度，一个值等信息，并导出到Excel 中
发布时间：2018-08-22 16:22:44 所属栏目：站长百科 来源：站长网
导读：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,@sample nvarchar(40) SELECT d.name TableName,a.name FieldName,b.name TypeName,a.le
|
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,@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 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=@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 from #tc order by tablename DROP TABLE #tc --select * from ##tx declare @db nvarchar(60),@sql nvarchar(3000) set @db=db_name() --请修改用户名和口令 导出到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 （编辑：核心网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
