Get database name of table using SQL Server

Question

I have the following SQL query which is selecting all of the column names based on some search criteria, however; I want to get the database name as well. How would I do this?

I have not been able to accomplish this with a join as of now. I am not sure what the relationship is between sys.tables and sys.databases. Any guidance would be appreciated.

SELECT
    c.name  AS 'ColumnName',
    t.name AS 'TableName'
FROM 
    sys.columns c
JOIN 
    sys.tables t ON c.object_id = t.object_id
WHERE 
    c.name LIKE '%Broker%'
ORDER BY 
    TableName, ColumnName;

Thank you. I was able to accomplish my goal by using the below code with your guidance. For further clarity I added the schema.

SELECT      
    '[' + SCHEMA_NAME(schema_id) + ']' + '.' +  '[' + db_name() + ']' as "Database_Schema_Name",
    t.name AS 'Table_Name',
    c.name  AS 'Column_Name'
FROM        
    sys.columns c
JOIN
    sys.tables t ON c.object_id = t.object_id
WHERE       
    c.name LIKE '%Broker%'
ORDER BY    
    Table_Name, Column_Name

Gordon Linoff · Accepted Answer · 2018-01-18 16:03:29Z

2

In SQL Server, the system tables are stored per database. Hence, all is in one database, so you can just use dbname():

SELECT db_name() as DatabaseName, c.name AS ColumnName, t.name AS TableName
FROM sys.columns c JOIN
     sys.tables t
     ON c.object_id = t.object_id
WHERE c.name LIKE '%Broker%'
ORDER BY TableName, ColumnName;

Note: Because INFORMATION_SCHEMA tables are standard across databases, I prefer to get metadata information from those tables when available. This also has the advantage that you only need INFORMATION_SCHEMA.COLUMNS, so no JOIN is (explicitly) necessary.

answered Jan 18, 2018 at 16:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bibin Mathew · Accepted Answer · 2018-01-18 16:02:52Z

0

You can use this

SELECT DB_NAME(3)AS [Database Name];

answered Jan 18, 2018 at 16:02

Bibin Mathew

4653 silver badges11 bronze badges

Collectives™ on Stack Overflow

Get database name of table using SQL Server

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related