Is their any other way or sql query to find the database table names with a particular column than shown below,
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'NameID'
-
3Why would you need another way?Giannis Paraskevopoulos– Giannis Paraskevopoulos2013-08-13 14:21:11 +00:00Commented Aug 13, 2013 at 14:21
-
1That's the way if you want to use standards SQL. If you don't, then you need to add a tag for your specific RDBMS product (e.g. Oracle, MySQL, etc)Damien_The_Unbeliever– Damien_The_Unbeliever2013-08-13 14:21:14 +00:00Commented Aug 13, 2013 at 14:21
-
this might differ depending on the RDBMS you are using.Randy– Randy2013-08-13 14:21:16 +00:00Commented Aug 13, 2013 at 14:21
-
watch out querying INFORMATION_SCHEMA should be very heavy for your server.Raymond Nijland– Raymond Nijland2013-08-13 14:27:36 +00:00Commented Aug 13, 2013 at 14:27
-
that's why i was asking for another approach..RobertKing– RobertKing2013-08-13 15:24:12 +00:00Commented Aug 13, 2013 at 15:24
|
Show 3 more comments
3 Answers
In SQL Server, you can query sys.columns.
Something like:
SELECT
t.name
FROM
sys.columns c
inner join
sys.tables t
on
c.object_id = t.object_id
WHERE
c.name = 'NameID'
You might want an additional lookup to resolve the schema name, if you have tables in multiple schemas.
answered Aug 14, 2013 at 6:43
Damien_The_Unbeliever
241k28 gold badges358 silver badges470 bronze badges
Comments
you can run this query
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Column%' -- write the column you search here
ORDER BY schema_name, table_name;
Comments
For Oracle Database. Use the below query:
select table_name from all_tab_columns where column_name = 'NameID';
If you’ve got DBA privileges, you can try this command instead:
select table_name from dba_tab_columns where column_name = 'NameID';
