msSQL查寻有指定字段的所有表

比如一个数据库有100个表，想得到有ID字段的所有表名

答案：use DBName--指定数据库
go
select  a.Name as TabName,b.Name as ColName  from sysobjects as a inner join syscolumns as b on a.ID=b.ID and a.xtype='U' and b.Name='ID'

其他：SELECT 
  table_schema AS 数据库名, 
  table_name AS 表名
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  COLUMN_NAME = 'ID' SELECT
 DISTINCT  c.TABLE_NAME
FROM information_schema.COLUMNS c
WHERE c.TABLE_SCHEMA = 'mysql'
    AND LOWER(c.COLUMN_NAME) = 'ID';

c.TABLE_SCHEMA = 'mysql' 换成你的库名就行 

上一个：mssql的备份与还原
下一个：mssql_query中文出错