How to find out Table from the Whole Database

Posted by Self-Innovator under Sql Server on 9/5/2012 | Points: 10 | Views : 2039 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi Good eve,
How can i find out the table from the database where i have only specific column's of the table. I wanted to find out under which table these columns belongs to....It's very emergency please provide me the query to filter the table.

Join Hands Change lives
Thanks & Regards
Straight Edge Society

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: San.Pblr.Gct on: 9/5/2012 [Member] Starter | Points: 25

0	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 '%booking_id%' ORDER BY schema_name, table_name; Here booking_id is my column name Self-Innovator, if this helps please login to Mark As Answer. \| Alert Moderator

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 '%booking_id%'

ORDER BY schema_name, table_name;

Here booking_id is my column name

Self-Innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vasanthmvp on: 9/5/2012 [Member] Starter | Points: 25

0	Hi San.Pblr.Gct, the query is good. But its showing just a table with schema. Can you be more clear on this. Thanks, Awesome Coding !! :) Self-Innovator, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Vuyiswamb on: 9/5/2012 [Member] [MVP] [Administrator] NotApplicable | Points: 25

0	RegGate has a product that allows you to search fields , sp's, tables, views and any other objects in the DB http://www.red-gate.com/products/sql-development/sql-search/ Thank you for posting at Dotnetfunda [Administrator] Self-Innovator, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: San.Pblr.Gct on: 9/5/2012 [Member] Starter | Points: 25

0	what else you want.? The above query displays in which table the columns are present and i believe you wanted to find all the tables in which that column is present. Self-Innovator, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Hariinakoti on: 9/5/2012 [Member] Starter | Points: 25

0	Nice explanation Mr.San.Pblr.Gct Thanks & Regards Hari Self-Innovator, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts