CODEWITHSUNDEEP
sqlsql-server-2005
Naveen
Naveen

Reputation: 119

How to find the database name and table name by using Column name

I need a query in Sql server 2005. I have a column name. Having that, I need to find a database name and table name. Is there any way to find it?

Upvotes: 2

Views: 3464

Answers (2)

Devart
Devart

Reputation: 121902

Possible, this script will be helpful for you -

DECLARE @SearchColumnName SYSNAME
SELECT @SearchColumnName = 'EmployeeID'

IF EXISTS(
    SELECT 1 
    FROM tempdb.dbo.sysobjects 
    WHERE id = OBJECT_ID('tempdb.dbo.#DB')
) DROP TABLE #DB

CREATE TABLE #DB
(
      [DB_NAME] SYSNAME
    , [OBJECT_NAME] SYSNAME  
    , [COLUMN_NAME] SYSNAME
)

DECLARE @SQL NVARCHAR(500)
SELECT @SQL = 'USE [?]

INSERT INTO #DB
(
      [DB_NAME]
    , [OBJECT_NAME]  
    , [COLUMN_NAME]
)
SELECT 
      DB_NAME()
    , s.name + ''.'' + o.name
    , c.name
FROM (
    SELECT 
          c.[object_id]
        , c.name
    FROM sys.columns c WITH (NOWAIT)
    WHERE c.name LIKE ''%'' + ''' + @SearchColumnName+ ''' + ''%''
) c
JOIN sys.objects o WITH (NOWAIT) ON c.[object_id] = o.[object_id]
JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
WHERE o.type = ''U'''

EXEC sys.sp_MSforeachdb @SQL

SELECT *
FROM #DB
ORDER BY [DB_NAME], [OBJECT_NAME]

Upvotes: 0

paulsm4
paulsm4

Reputation: 121599

From SO's sister site, "StackExchange":

https://dba.stackexchange.com/questions/511/how-to-list-search-all-columns-in-a-sql-server-2008-databases

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 '%EmployeeID%'
ORDER BY schema_name, table_name;

Upvotes: 4

Related Questions