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

Question

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

Answer 1

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'

Answer 2

SELECT
table_schema AS 数据库名,
table_name AS 表名
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME = 'ID'

Answer 3

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' 换成你的库名就行