T-SQL检测表无用字段

把所有字段中，如果某些字段全是0或者全是空或者全是“null”的过滤掉，把空的字段名称和长度显示出来，如果要显示非空的字段隐藏空的可以把not exists的not去掉就可以了

CREATE PROCEDURE dbo.sp_chk_no_used_field @tname as varchar(266) --检测表中无用字段 by Sgxcn 2011.9 AS BEGIN declare @fname nvarchar(255)
declare curField cursor for SELECT name FROM Syscolumns WHERE ID=OBJECT_ID(@tname) and name is not NULL

create table ##temp (name nvarchar(255))

open curField FETCH NEXT FROM curField into @fname WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @SQL as NVARCHAR(255) --如果要显示非空的字段隐藏空的可以把not exists的not去掉就可以了 SET @SQL = 'if not exists(select * from '+@tname+ ' where (' + @fname + ' is not null) or (' + @fname + ' <> '''') or (' + @fname + ' <> 0)) insert into ##temp values('''+@fname+ ''')' EXEC sp_executesql @SQL FETCH NEXT FROM curField into @fname END CLOSE curField DEALLOCATE curField

SELECT [name], [length] FROM Syscolumns WHERE ID=OBJECT_ID(@tname) and name in (SELECT [name] FROM ##temp)

drop table ##temp END GO

调用示例

exec sp_chk_no_used_field '表名'

转载于:https://www.cnblogs.com/treryh/p/3184076.html