select name from sys.tables where name like '%JL%'2000以前版本: select name from sysobjects where name like '%JL%' and xtype = 'u'
1:从系统视图 sys.tables sys.columns 查询出所有 包含 name的字段 2:滚一下上一个查询的结果集,并 执行 select count(name) from 表 where name like '%JL%' 3: 将存在的 表名 查入一个临时表 4:select * from 临时表
JL是列名? 那这样查select name from sys.tables a where exists ( select * from sys.columns where object_id = a.object_id and name = 'JL' ) 2000以前版本: select name from sysobjects a where xtype= 'u' and exists ( select * from syscolumns where id = a.id and name = 'JL' )
1:从系统视图 sys.tables sys.columns 查询出所有 包含 name的字段 2:滚一下上一个查询的结果集,并 执行 select count(name) from 表 where name like '%JL%' 3: 将存在的 表名 查入一个临时表 4:select * from 临时表
declare @name varchar(50) declare @t table (name varchar(20)) declare cur cursor for select object_name(c.object_id) from sys.columns c,sys.tables o where c.name='name' and c.object_id=o.object_idopen cur fetch next from cur into @name while @@fetch_status=0 begin insert @t exec('select name from '+@name+' where charindex(''JL'',NAME)>0')
fetch next from cur into @name end close cur deallocate cur select * from @t
DECLARE @tb TABLE (tbName VARCHAR(100)) DECLARE @s VARCHAR(100)DECLARE curName CURSOR FOR SELECT k.name FROM syscolumns s JOIN sysobjects k ON s.id=k.id WHERE s.name='dbname' AND k.type='u';OPEN curName;FETCH next FROM curName INTO @s WHILE @@FETCH_STATUS=0 BEGIN INSERT @tb exec('select '''+@s+''' from '+@s+' where dbname like ''%JL%'''); FETCH next FROM curName INTO @s END close curName deallocate curName select * from @tb
select name from sysobjects where name like '%JL%' and xtype = 'u'
2:滚一下上一个查询的结果集,并 执行 select count(name) from 表 where name like '%JL%'
3: 将存在的 表名 查入一个临时表
4:select * from 临时表
那这样查select name from sys.tables a
where exists (
select * from sys.columns
where object_id = a.object_id
and name = 'JL'
)
2000以前版本:
select name from sysobjects a
where xtype= 'u'
and exists (
select * from syscolumns
where id = a.id
and name = 'JL'
)
1:从系统视图 sys.tables sys.columns 查询出所有 包含 name的字段
2:滚一下上一个查询的结果集,并 执行 select count(name) from 表 where name like '%JL%'
3: 将存在的 表名 查入一个临时表
4:select * from 临时表
declare @t table (name varchar(20))
declare cur cursor for
select object_name(c.object_id)
from sys.columns c,sys.tables o
where c.name='name' and c.object_id=o.object_idopen cur
fetch next from cur into @name
while @@fetch_status=0
begin
insert @t exec('select name from '+@name+' where charindex(''JL'',NAME)>0')
fetch next from cur into @name
end
close cur
deallocate cur
select * from @t
DECLARE @s VARCHAR(100)DECLARE curName CURSOR FOR
SELECT k.name
FROM syscolumns s JOIN sysobjects k ON s.id=k.id
WHERE s.name='dbname' AND k.type='u';OPEN curName;FETCH next FROM curName INTO @s WHILE @@FETCH_STATUS=0
BEGIN
INSERT @tb
exec('select '''+@s+''' from '+@s+' where dbname like ''%JL%''');
FETCH next FROM curName INTO @s
END
close curName
deallocate curName
select * from @tb
所以只通过sys.tables与sys.columns怎么能得到某个table的某个字段的具体数据内容呢?