I am in spree to find a SQL or T-SQL query to get names of all the tables in a Database/Schema which have a column o开发者_StackOverflowf given name.
Can any one help me in this please?
Thanks in Advance :)
This will find the columns you're after in the current/active database. If you're in a different database, just prefix INFORMATION_SCHEMA.COLUMNS
with the database name (e.g. dbname.INFORMATION_SCHEMA.COLUMNS
)
DECLARE @Schema varchar(max)
DECLARE @Column varchar(max)
SET @Schema = NULL
SET @Column = 'primary_id'
SELECT DISTINCT
TABLE_NAME, *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = @Column
AND
(
@Schema IS NULL
OR TABLE_SCHEMA = @Schema
)
This will work in SQL Server and should work in other DBMS's too since INFORMATION_SCHEMA is somewhat standardised (e.g. here it is in MySql: http://dev.mysql.com/doc/refman/5.0/en/columns-table.html)
You can use the INFORMATION_SCHEMA.COLUMNS
system view:
SELECT DISTINCT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Name of column';
This query will give you all tables with a column named foo
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'foo'
Be aware that while correct for SQL*Server (and mySQL - and postGres) the answer is different for Oracle, as they don't support the Information schema (which is ANSI SQL-92, so they have had plenty of time to do so).
On Oracle you would use
SELECT table_name, column_name
FROM ALL_TAB_COLUMNS
WHERE column_name = :column_name
Or you could create a schema and view to do the job of the missing information_schema.columns.
select TABLE_NAME from Your_Database_Name.INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME like '%ColumnName%' group by TABLE_NAME
in sybase;
declare @column varchar(30)
select @column = 'column_name'
select object_name(id), @column from DB_NAME..syscolumns where name = @column
精彩评论