把所有字段中,如果某些字段全是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 '表名'
以上是一部分介绍,如需要完整的资料或者如不符合您的要求,请联系技术人员qq:242219979咨询