/**/
/*
谁可以帮我写个SQL执行后返回的列分别是:表名、列名、列类型、列长度、列描述、是否主键
写完整来。就是我一拿过来就可以运行的那种!~!~谢谢哦。
*/
SELECT
( case when a.colorder = 1 then d.name else '' end ) N ' 表名 ' ,
a.colorder N ' 字段序号 ' ,
a.name N ' 字段名 ' ,
( case when COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1 then ' √ ' else '' end ) N ' 标识 ' ,
( case when ( SELECT count ( * )
FROM sysobjects
WHERE (name in
( SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
( SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
( SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = ' PK ' )) > 0 then ' √ ' else '' end ) N ' 主键 ' ,
b.name N ' 类型 ' ,
a.length N ' 占用字节数 ' ,
COLUMNPROPERTY (a.id,a.name, ' PRECISION ' ) as N ' 长度 ' ,
isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 ) as N ' 小数位数 ' ,
( case when a.isnullable = 1 then ' √ ' else '' end ) N ' 允许空 ' ,
isnull (e. text , '' ) N ' 默认值 ' ,
isnull (g. [ value ] , '' ) AS N ' 字段说明 '
-- into ##tx
FROM syscolumns a left join systypes b
on a.xtype = b.xusertype
inner join sysobjects d
on a.id = d.id and d.xtype = ' U ' and d.name <> ' dtproperties '
left join syscomments e
on a.cdefault = e.id
left join sysproperties g
on a.id = g.id AND a.colid = g.smallid
order by object_name (a.id),a.colorder
-- 方法二
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
select * from ##tx
谁可以帮我写个SQL执行后返回的列分别是:表名、列名、列类型、列长度、列描述、是否主键
写完整来。就是我一拿过来就可以运行的那种!~!~谢谢哦。
*/
SELECT
( case when a.colorder = 1 then d.name else '' end ) N ' 表名 ' ,
a.colorder N ' 字段序号 ' ,
a.name N ' 字段名 ' ,
( case when COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1 then ' √ ' else '' end ) N ' 标识 ' ,
( case when ( SELECT count ( * )
FROM sysobjects
WHERE (name in
( SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
( SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
( SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = ' PK ' )) > 0 then ' √ ' else '' end ) N ' 主键 ' ,
b.name N ' 类型 ' ,
a.length N ' 占用字节数 ' ,
COLUMNPROPERTY (a.id,a.name, ' PRECISION ' ) as N ' 长度 ' ,
isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 ) as N ' 小数位数 ' ,
( case when a.isnullable = 1 then ' √ ' else '' end ) N ' 允许空 ' ,
isnull (e. text , '' ) N ' 默认值 ' ,
isnull (g. [ value ] , '' ) AS N ' 字段说明 '
-- into ##tx
FROM syscolumns a left join systypes b
on a.xtype = b.xusertype
inner join sysobjects d
on a.id = d.id and d.xtype = ' U ' and d.name <> ' dtproperties '
left join syscomments e
on a.cdefault = e.id
left join sysproperties g
on a.id = g.id AND a.colid = g.smallid
order by object_name (a.id),a.colorder
-- 方法二
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
select * from ##tx