超难的数据库查询删除操作,跪求高手解答

fcchao 2010-09-16 03:52:35
一个数据库 里面有近1000张表,我不知道表名,也不知道表里的字段名
我现在要删除这些表里 某个字段含有K216这几个字符的所有数据,整行删除的,怎么操作啊?
愁死我了,跪求高手帮忙
...全文
60 点赞 收藏 6
写回复
6 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
fcchao 2010-09-17
[Quote=引用 5 楼 xman_78tom 的回复:]
SQL code

declare @id int, @tabname varchar(256),@colname sysname;
declare @sql varchar(1000);

declare tab_c cursor local for
select id from sysobjects where type='U';

open tab_c;

fetch……
[/Quote]
代码是没问题,貌似可以删,但是我执行了后,在客户端查询,怎么还是有呢?郁闷啊,是删的哪里的啊?
回复
xman_78tom 2010-09-16

declare @id int, @tabname varchar(256),@colname sysname;
declare @sql varchar(1000);

declare tab_c cursor local for
select id from sysobjects where type='U';

open tab_c;

fetch next from tab_c into @id;
while @@fetch_status=0
begin
set @tabname=quotename(object_schema_name(@id))+'.'+quotename(object_name(@id));

declare col_c cursor local for
select name from syscolumns where id=@id and xtype in (175,167,231,239) and length>=4;
open col_c;
fetch next from col_c into @colname;
while @@fetch_status=0
begin
set @sql='delete from '+@tabname+' where '+quotename(@colname)+' like ''%K216%''';
print @sql;
-- exec(@sql);
fetch next from col_c into @colname;
end
close col_c;
deallocate col_c;

fetch next from tab_c into @id;
end

close tab_c;

回复
fcchao 2010-09-16
我不知道表名,也不知道列名,只知道列里的数据含有K216的,全删了
回复
fcchao 2010-09-16
其实主要就是先能查询出哪些表里含有我不知道名的那个字段,我什么都不知道,只知道那个字段里某些行含有K216这几个字符。有可能查出来吗?
回复
喜-喜 2010-09-16
'参考:获取数据库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 行受影响)
*/
回复
东那个升 2010-09-16
是数据含有K216,还是列名称含有K26
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-09-16 03:52
社区公告
暂无公告