查询所有表(N个表)所有字段(N个)中某值("部门负责")的方法请高手指点下,谢了。
解决方案 »
- sql server 2005上网很难下啊,是不是要买碟啊
- 按顺序更新100个怎么做?
- mysql server如何登陆启用啊
- sql竖表变横表,及NULL值聚合,求助
- 邹老大的分页存储过程的问题
- 在sql2000中varchar(8000),text,ntext字段类型中,存储两三千字的文字可是存储时只截取其中的七八百字,其他的无法存储,用命令好还是在管理器中直接在表中存好,该如何存???
- 熟悉数据库邮件的朋友看过来。
- 請問在SQL中存儲大型文本用什么方法,最好就一個欄位
- 请问如何在查询分析器中执行DTS或sql server agant中的jobs
- 求大神帮助
- 如何在多表单中查找指定内容的字段?
- XP PRO 下安装 SQL 2005企业版+VS2008 TS版本
declare @tableName nvarchar(256), @sql nvarchar(4000)
set @tableName=parsename(''?'',1)
set @sql=N''''select @sql=@sql+N'' union all select ''''''+@tableName+'''''' as tableName from ''+@tableName+'' where charindex(N''''部门负责'''',''+name+'')>0''
from syscolumns
where id=object_id(@tableName) and type_name(xtype) in (''varchar'',''nvarchar'',''char'',''nchar'')set @sql=stuff(@sql,1,10,'''')
--print @sql
exec(@sql)
'
感觉面熟...'获取数据库test中含有null值的数据表及字段名'
use test
go
if object_id('test.dbo.tb1') is not null drop table tb1
-- 创建数据表
create table tb1
(
a1 char(2),
b1 char(2),
c1 int
)
go
--插入测试数据
insert into tb1 select 'A','B',2
union all select 'A','C',3
union all select 'A','D',null
union all select 'B',null,3
union all select 'B','E',8
union all select 'E','F',null
union all select 'E','G',3
go
if object_id('test.dbo.tb2') is not null drop table tb2
-- 创建数据表
create table tb2
(
a2 char(2),
b2 char(2),
c2 int
)
go
--插入测试数据
insert into tb2 select 'A','B',2
union all select 'A','C',null
union all select 'A','D',5
union all select 'B','C',3
union all select null,'E',null
union all select 'E','F',2
union all select 'E','G',3
go
if object_id('test.dbo.tb3') is not null drop table tb3
-- 创建数据表
create table tb3
(
a3 char(2),
b3 char(2),
c3 int
)
go
--插入测试数据
insert into tb3 select 'A','B',2
union all select 'A','C',3
union all select 'A','D',5
union all select 'B','C',3
union all select 'B','E',8
union all select 'E','F',2
union all select 'E','G',3
go
-->代码实现
if object_id('test.dbo.result') is not null drop table result
create table result(tbname varchar(30),tbcolumn varchar(30))--存储结果
--获取数据库 test 中的所有表名:
if object_id('test.dbo.nametemptable') is not null drop table nametemptable
create table nametemptable(tbname varchar(30))--存储表名
insert into nametemptable
select name from test..sysobjects where type='u' and name not in('columntemptable','nametemptable','result')
--选出 null 字段:
declare @tbname varchar(30),
@colname varchar(30),
@sqlstr varchar(max),
@tb_i int,
@tb_total int,
@col_i int,
@col_total int
select @tb_total=count(*),@tb_i=1 from nametemptable
while(@tb_i<=@tb_total)
begin
if object_id('test.dbo.columntemptable') is not null drop table columntemptable
create table columntemptable(tbcolumn varchar(30))
select top 1 @tbname=tbname from nametemptable
where tbname not in (select top (@tb_i-1) tbname from nametemptable)
exec('insert into columntemptable
Select Name FROM SysColumns Where id=Object_Id('''+@tbname+''')')
select @col_total=count(*),@col_i=1 from columntemptable
while(@col_i<=@col_total)
begin
select top 1 @colname=tbcolumn from columntemptable
where tbcolumn not in (select top (@col_i-1) tbcolumn from columntemptable)
exec('if exists(select * from '+@tbname+' where '+@colname+' is null)
insert into result select '''+@tbname+''','''+@colname+'''')
set @col_i=@col_i+1
end
set @tb_i=@tb_i+1
drop table columntemptable
end
--测试:
select * from result
/*测试结果:tbname tbcolumn
-----------------------
tb1 b1
tb1 c1
tb2 a2
tb2 c2(4 行受影响)
*/
drop table nametemptable,result
################################## 方法二 #################################IF OBJECT_ID('TEMPDB..#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T(TBNAME NVARCHAR(20),COLNAME NVARCHAR(20))
SET NOCOUNT ON
DECLARE @S NVARCHAR(4000)
DECLARE C CURSOR FOR
SELECT 'IF EXISTS(SELECT 1 FROM ['+B.NAME+'] WHERE ['+A.NAME+'] IS NULL)
INSERT #T SELECT '''+B.NAME+''','''+A.NAME+''''
FROM SYSCOLUMNS A JOIN SYSOBJECTS B ON A.ID=B.ID AND B.TYPE='U'
OPEN C
FETCH C INTO @S
WHILE @@FETCH_STATUS=0
BEGIN
EXEC(@S)
FETCH C INTO @S
END
CLOSE C
DEALLOCATE C
SET NOCOUNT OFF
SELECT * FROM #T
/*
TBNAME COLNAME
-------------------- --------------------
tb1 b1
tb1 c1
tb2 a2
tb2 c2(4 行受影响)
*/
2、用ms_foreachtable试试