高分求救!如何建立一个存储过程能检测一个数据库中所有表中的字段是否为空值及合法性？

有以下字段:
字段              类型            取值范围
fcourseNO    char(4)      0000-9999
forderNo      int              >0的整数
能在一个数据库中查询这两个字段,及查询到有此字段相关表中的记录是否在以上规定的取值范围!
谢谢!

6 个解决方案

#1

create proc check_1 @dbname nvarchar(255)
as

declare @n nvarchar(255)
create table ##temp (name nvarchar(255))
declare a cursor for
select sysobjects.name from sysobjects,syscolumns
where sysobjects.xtype = 'u' and sysobjects.name <> 'dtproperties' and syscolumns.name in ('fcourseNO','forderNo' )

open a
FETCH NEXT FROM a into @n
WHILE @@FETCH_STATUS = 0
BEGIN
' if exists(select * from '+@n+' where fcourseNO is null or (fcourseNO >=''0000'' and fcourseNO <=''9999'') or forderNo is null or forderNo< 0 )
insert into ##temp values('''+@n+''')'

FETCH NEXT FROM a into @n
END
CLOSE a
DEALLOCATE a
select * from ##temp --返回不符合规则的表
drop ##temp

GO

#2

--没有用到数据库名，你在使用前，use 吧
create proc check_1
as

declare @n nvarchar(255)
create table ##temp (name nvarchar(255))
declare a cursor for
select sysobjects.name from sysobjects,syscolumns
where sysobjects.xtype = 'u' and sysobjects.name <> 'dtproperties' and syscolumns.name in ('fcourseNO','forderNo' )

open a
FETCH NEXT FROM a into @n
WHILE @@FETCH_STATUS = 0
BEGIN
' if exists(select * from '+@n+' where fcourseNO is null or (fcourseNO >=''0000'' and fcourseNO <=''9999'') or forderNo is null or forderNo< 0 )
insert into ##temp values('''+@n+''')'

FETCH NEXT FROM a into @n
END
CLOSE a
DEALLOCATE a
select * from ##temp --返回不符合规则的表
drop ##temp

GO

#3

你看一下：
http://expert.csdn.net/Expert/FAQ/FAQ_Index.asp?id=8800
它能查出某张表的字段的系统信息。
你在这个基础上去循环一下表名就可以了。

#4

select * from
table where fcourseNO>'0000' and fcourseNO<'9999' and
forderNo >0
如果只以上数据是有效的，可以建立约束

#5

proc check
@return output
as
if (select * from
table where fcourseNO>'0000' and fcourseNO<'9999' and
forderNo >0)
set @return =1
else
set @return = 0

#6

建立约束
forderNo>0
(len([fcourseNO]) = 4 and convert(int,[a]) >= 0 and convert(int,[a]) <= 99999

#1

#2

#3