Get all tables and Databases in SQL Server

If you’re working with SQL Server and need to explore your database structure, this script will help you list all databases and their corresponding tables quickly. Whether you’re a database administrator, developer, or data analyst, having a clear overview of your SQL Server environment is essential. In this guide, we’ll walk through a simple SQL script that retrieves all database names along with the tables they contain. This is especially useful for auditing, documentation, or managing large-scale SQL Server instances. Follow along to streamline your database discovery process with this SQL Server query.

	declare @Databases table (
	ID int,
	Name varchar(100),
	create_date smalldatetime,
	compatability_level int,
	state_desc varchar(50),
	user_access_desc varchar(100),
	log_reuse_wait_desc varchar(50),
	recover_model varchar(50),
	is_read_only smallint,
	is_auto_shrink_on bit
	)
	insert @Databases
	select database_id,name,create_date,compatibility_level,state_desc,user_access_desc,log_reuse_wait_desc,recovery_model_desc,is_read_only ,is_auto_shrink_on
	from sys.databases
	select * from @databases
	declare @tables table (
	object_id bigint,
	name varchar(100),
	fullname varchar(100),
	schemaid int,
	create_date smalldatetime,
	modify_date smalldatetime,
	max_column_id_used bigint,
	is_replicated bit,
	is_tracked_by_cdc bit,
	lock_escalation bit,
	is_memory_optimized bit,
	temporal_type bit
	)
	insert @Tables
	select object_id,name, schema_name(schema_id)+'.'+name fullname ,schema_id, create_date,modify_date,max_column_id_used,is_replicated,is_tracked_by_cdc,lock_escalation,is_memory_optimized,temporal_type
	from sys.tables
	where is_ms_shipped=0
	select *
	from @Tables

view raw GetDatabaseAndTables.sql hosted with by GitHub

below is the output of the queries, for all the databases and then another query for all the tables. note that the tables query, will give you all the tables in the database session you are using, so make sure to change it to the databases you want. I have iterated through the collection of tables with msp_foreach in the past.

With this SQL Server script, you can quickly gain insight into all your databases and their tables in one go. It’s a practical tool for managing, auditing, or documenting your SQL Server environment. Save time and reduce manual effort by automating database discovery. Start using this script today to streamline your SQL Server workflows.