CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008metadata
user489041
user489041

Reputation: 28312

Accessing Database Meta Data

Im running into a tough issue. I have a database using Microsoft SQL 2008 and in this database there are many tables. The tables were auto generated and do not have meaningful names. There is one particular table that I need, and I can not seem to find it.

I know what the names of a few of the columns in the table are called. Is there a way I can go through all the tables one at a time looking at the names of the columns and seeing if they match the ones I know.

If they do, then I can look farther into it the table to see if it is the one I am looking for. Does this sound like a good approach to the problem? Is it possible? Any ideas of where to start?

Upvotes: 1

Views: 146

Answers (3)

Sualeh Fatehi
Sualeh Fatehi

Reputation: 4784

With the scripts above, you are limited to SQL wild-carding, which can be pretty limited. You can use SchemaCrawler grep to more powerfully search through your database using regular expressions. SchemaCrawler also allows you additional features to to look for tables related by foreign keys, so for example, you can say find me all tables that have a customer address column, along with the tables that refer to these tables. SchemaCrawler is a command-line tool that is bundled with a Microsoft SQL Server database driver.

Sualeh Fatehi, SchemaCrawler

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52675

Alternative to Aaron's answer using Information_schema.columns instead of sys.columns

SELECT Table_name
FROM 
      information_schema.columns
WHERE 
      column_name IN ('column 1', 'column 2')
GROUP BY Table_Name
Having COUNT(column_name) = 2

See this Data.SE query for a working example

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

SELECT OBJECT_SCHEMA_NAME([object_id]),
    OBJECT_NAME([object_id]) 
    FROM sys.columns 
    WHERE name IN ('column 1', 'column 2' 
    /* , ... other columns */);

EDIT by request, in case the OP meant to identify ALL vs. ANY:

SELECT OBJECT_SCHEMA_NAME([object_id), name 
FROM sys.tables AS t
WHERE EXISTS 
(
    SELECT 1 FROM sys.columns
    WHERE name = 'column 1'
    AND [object_id] = t.[object_id]
)
AND EXISTS 
(
    SELECT 1 FROM sys.columns
    WHERE name = 'column 2'
    AND [object_id] = t.[object_id]
)
/* ... repeat for other columns ... */

Upvotes: 2

Related Questions