如何在数据库中查询某个值所对应的相关表呢!
sql2000系统表的应用
--1:获取当前数据库中的所有用户表
select name from sysobjects where xtype=u and status>=0
--2:获取某一个表的所有字段
select name from syscolumns where id=object_id(表名)
--3:查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like %表名%
--4:查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype=p
--5:查询用户创建的所有数据库
select * from master..sysdatabases d where sid not in(select sid from master..syslogins where name=sa)
或者
select dbid, name as db_name from master..sysdatabases where sid <> 0x01
--6:查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = 表名
--7:取得表字段的描述
select name,
(select value from sysproperties where id = syscolumns.id and smallid=syscolumns.colid) as 描述
from syscolumns where id=object_id(ic卡公司信息)
一列名为“name”
如何找到这列名所在数据库中对应的表名呢
select b.name
from syscolumns a join sysobjects b on a.id = b.id
where b.type = u and a.name = name
给定一个任意的字段的值,查询出它属于的表及哪个字段的sql语句:
declare @str varchar(100)
set @str=ph --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select s=if exists(select 1 from [+b.name+] where [+a.name+] like %+@str+%)
print 所在的表及字段: [+b.name+].[+a.name+]
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype=u and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb