Getting database name from table name in SQL Server

Question

I need to get database name from table names in SQL Server. I can query 'tablenames' in the database, but how do I get the database name for each table as well?

select * 
from sys.tables;

Output:

name    object_id   principal_id    schema_id   parent_object_id    type    type_desc   create_date modify_date is_ms_shipped   is_published    is_schema_published lob_data_space_id   filestream_data_space_id    max_column_id_used  lock_on_bulk_load   uses_ansi_nulls is_replicated   has_replication_filter  is_merge_published  is_sync_tran_subscribed has_unchecked_assembly_data text_in_row_limit   large_value_types_out_of_row    is_tracked_by_cdc   lock_escalation lock_escalation_desc    is_filetable    is_memory_optimized durability  durability_desc temporal_type   temporal_type_desc  history_table_id    is_remote_data_archive_enabled  is_external
DL_CONLog   719055  NULL    1   0   U   USER_TABLE  43:24.3 01:41.0 0   0   0   0   NULL    10  0   1   0   0   0   0   0   0   0   0   0   TABLE   0   0   0   SCHEMA_AND_DATA 0   NON_TEMPORAL_TABLE  NULL    0   0
DL_TIMJobImportLinkBrokerToModule   771110  NULL    1   0   U   USER_TABLE  45:12.1 45:20.7 0   0   0   1   NULL    64  0   1   0   0   0   0   0   0   0   0   0   TABLE   0   0   0   SCHEMA_AND_DATA 0   NON_TEMPORAL_TABLE  NULL    0   0

Please show us what is being used to get the 'tablenames'. Server name is usually part of the connection string. — donPablo
– donPablo, Commented Jun 11, 2019 at 0:56
If you can query the table names, you're already connected to the database, so you know what the database name is already. How else would you ask it for the list of table names? — Ken White
– Ken White, Commented Jun 11, 2019 at 0:58
I have edited my question., I guess sys.tables keep the information for all tables across databases. — Allianz1
– Allianz1, Commented Jun 11, 2019 at 1:32

Sreenu131 · Accepted Answer · 2019-06-11 12:58:58Z

1

Try using

SELECT * FROM  INFORMATION_SCHEMA.TABLES

edited Jun 11, 2019 at 12:58

Sreenu131

2,5361 gold badge9 silver badges20 bronze badges

answered Jun 11, 2019 at 4:55

Niharika

114 bronze badges

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

Comments

Squirrel · Accepted Answer · 2019-06-11 01:53:23Z

0

sys.tables gives you list of table of current database. The current database name can be query from db_name().

If you are looking for a list of tables of a specific database, use db1.sys.tables

Not really sure what is your requirement, but if you want current database & table name, you can query INFORMATION_SCHEMA.TABLES

answered Jun 11, 2019 at 1:53

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Collectives™ on Stack Overflow

Getting database name from table name in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related