CODEWITHSUNDEEP
sql-server
Allianz1
Allianz1

Reputation: 31

Getting database name from table name in SQL Server

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

Upvotes: 0

Views: 756

Answers (2)

Niharika
Niharika

Reputation: 11

Try using 

SELECT * FROM  INFORMATION_SCHEMA.TABLES

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

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

Upvotes: 0

Related Questions