1.已知字段名,查某个字段在哪些表中出现的情况:
use schoostdinfo
go
select b.name as tablename,a.name as columnname
from syscolumns a inner join sysobjects b
on a.id=b.id and b.type='u'
and a.name='已知的列名'
2.未知列名,查所有在不同表里出现过的列名
select o.name as tablename,s1.name as columnname
from syscolumns s1,sysobjects o
where s1.id=o.id and o.type='u' and
exists(
select 1 from syscolumns s2 where s1.name=s2.name and s1.id<>s2.id)
--1.如何用SQL语句取得数据库中的所有数据库的名字?
select name from master..sysdatabases
--2.如何用SQL语句取得数据库中某一数据库的的所有表的名字?
select name from dbname.dbo.sysobjects where xtype='u'
--3.如何用SQL语句取得数据库中某一表的的所有列的名字,列的类型,列的长度,是否为主键?
select name from dbname.dbo.syscolumns where id=object_id(N'tbname')
select a.name,
主键=case when b.indid=1 then 1 else 0 end
from syscolumns a left join sysindexkeys b
on a.id=b.id and a.colid=b.colid
where a.id=object_id('tbname')