TekOnline

SQL Server Schema Dump: One Copy-Paste Script to See Your Whole Database

You need to see a SQL Server database schema fast. You do not want to click through dozens of Object Explorer menus. This one copy-paste script lists every table, column, key, index, and row count in one run.

The script works on any SQL Server database. It creates nothing and changes nothing. It is read-only. You can reuse it on any server, any project, any time.

Why you need a schema dump script

You hit a database you have never seen before. You need to know its structure now. Object Explorer shows one table at a time. That takes too long.

A schema dump gives you the whole picture in one screen. You see the tables and their columns together. You see primary keys, foreign keys, and indexes. You see which table is large and which table is small.

This is useful when you debug an application issue. It is useful when you plan a migration. It is useful when you write a report query. It is useful when you rebuild a test database from production data.

How to use the script

  1. Open SQL Server Management Studio (SSMS).
  2. Connect to the server that hosts the database.
  3. Select the target database in the database selector.
  4. Open a new query window.
  5. Paste the script below into the window.
  6. Press F5 to run it.

The script returns one result table. Each row is one line of the dump. Copy the whole result in one action. To send the output to the Messages tab instead, press Ctrl+Shift+F (Results to Text) before you run the script, then copy from there.

The script

Copy the full script below. Save it as sql-server-schema-dump.sql. Then you always have it ready.

/* ============================================================================
   SQL SERVER FULL SCHEMA DUMP — copy-paste, reusable, single output
   ----------------------------------------------------------------------------
   Purpose : List tables, columns, primary keys, foreign keys, indexes, and
             row counts for any SQL Server database.
   Use     : Open SSMS, select the target database, paste, run.
             No stored procedures are created. Nothing is modified.
   Output  : ONE result table. Each row is one line of the dump.
             Copy the whole result in one action.
             Tip: press Ctrl+Shift+F (Results to Text) to send it to the
             Messages tab instead, then copy from there.
   ============================================================================ */
SET NOCOUNT ON;

DECLARE @lines TABLE (seq INT IDENTITY(1,1), line NVARCHAR(MAX));

/* ---------------------------------------------------------------------------
   1. TABLES WITH ROW COUNTS
--------------------------------------------------------------------------- */
INSERT INTO @lines (line) VALUES ('=== 1. TABLES (schema.table | row count) ===');
INSERT INTO @lines (line)
SELECT CONCAT(s.name, '.', t.name, CHAR(9), p.rows)
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0, 1)
ORDER BY s.name, t.name;
INSERT INTO @lines (line) VALUES ('');

/* ---------------------------------------------------------------------------
   2. COLUMNS (type, size, nullable, identity, default)
--------------------------------------------------------------------------- */
INSERT INTO @lines (line) VALUES ('=== 2. COLUMNS (table | # | column | type | size | nullable | identity | default) ===');
INSERT INTO @lines (line)
SELECT CONCAT(t.name, CHAR(9), c.column_id, CHAR(9), c.name, CHAR(9), ty.name, CHAR(9),
       CASE
           WHEN ty.name IN ('nvarchar', 'varchar', 'char', 'nchar') THEN CONCAT(c.max_length, ' chars')
           WHEN ty.name IN ('decimal', 'numeric') THEN CONCAT(c.precision, ',', c.scale)
           WHEN ty.name IN ('varbinary', 'binary') THEN CONCAT(c.max_length, ' bytes')
           ELSE ''
       END, CHAR(9),
       CASE WHEN c.is_nullable = 1 THEN 'yes' ELSE 'no' END, CHAR(9),
       CASE WHEN c.is_identity = 1 THEN 'yes' ELSE 'no' END, CHAR(9),
       ISNULL(dc.definition, ''))
FROM sys.tables t
JOIN sys.columns c ON c.object_id = t.object_id
JOIN sys.types ty ON ty.user_type_id = c.user_type_id
LEFT JOIN sys.default_constraints dc
       ON dc.parent_object_id = c.object_id
      AND dc.parent_column_id = c.column_id
ORDER BY t.name, c.column_id;
INSERT INTO @lines (line) VALUES ('');

/* ---------------------------------------------------------------------------
   3. PRIMARY KEYS
--------------------------------------------------------------------------- */
INSERT INTO @lines (line) VALUES ('=== 3. PRIMARY KEYS (table | column | ordinal) ===');
INSERT INTO @lines (line)
SELECT CONCAT(t.name, CHAR(9), c.name, CHAR(9), ic.key_ordinal)
FROM sys.tables t
JOIN sys.indexes i ON i.object_id = t.object_id AND i.is_primary_key = 1
JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
ORDER BY t.name, ic.key_ordinal;
INSERT INTO @lines (line) VALUES ('');

/* ---------------------------------------------------------------------------
   4. FOREIGN KEYS (table -> referenced table | fk name | fk column -> pk column)
--------------------------------------------------------------------------- */
INSERT INTO @lines (line) VALUES ('=== 4. FOREIGN KEYS (fk_table -> ref_table | fk_name | fk_column -> ref_column) ===');
INSERT INTO @lines (line)
SELECT CONCAT(OBJECT_NAME(fk.parent_object_id), ' -> ', OBJECT_NAME(fk.referenced_object_id),
       CHAR(9), fk.name, CHAR(9), pc.name, ' -> ', rc.name)
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
JOIN sys.columns pc ON pc.object_id = fk.parent_object_id AND pc.column_id = fkc.parent_column_id
JOIN sys.columns rc ON rc.object_id = fk.referenced_object_id AND rc.column_id = fkc.referenced_column_id
ORDER BY OBJECT_NAME(fk.parent_object_id), OBJECT_NAME(fk.referenced_object_id), fkc.constraint_column_id;
INSERT INTO @lines (line) VALUES ('');

/* ---------------------------------------------------------------------------
   5. NON-CLUSTERED INDEXES (key columns combined)
--------------------------------------------------------------------------- */
INSERT INTO @lines (line) VALUES ('=== 5. INDEXES (table | index | type | key_columns) ===');
INSERT INTO @lines (line)
SELECT CONCAT(t.name, CHAR(9), i.name, CHAR(9), i.type_desc, CHAR(9),
       STUFF((
           SELECT ', ' + c.name
           FROM sys.index_columns ic
           JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
           WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
           ORDER BY ic.key_ordinal
           FOR XML PATH('')
       ), 1, 2, ''))
FROM sys.tables t
JOIN sys.indexes i ON i.object_id = t.object_id
WHERE i.type > 0
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0
ORDER BY t.name, i.name;
INSERT INTO @lines (line) VALUES ('');

/* ---------------------------------------------------------------------------
   6. TABLES OUTSIDE THE 'dbo' SCHEMA
--------------------------------------------------------------------------- */
INSERT INTO @lines (line) VALUES ('=== 6. NON-DEFAULT SCHEMA TABLES ===');
INSERT INTO @lines (line)
SELECT CONCAT(s.name, '.', t.name)
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE s.name <> 'dbo'
ORDER BY s.name, t.name;

/* ---------------------------------------------------------------------------
   OUTPUT — ONE result table, copy in one action
--------------------------------------------------------------------------- */
SELECT line FROM @lines ORDER BY seq;

What each block shows

  • Block 1 lists every table with its row count. You see the big tables first.
  • Block 2 lists every column with its data type, size, and nullability.
  • Block 3 lists every primary key.
  • Block 4 lists every foreign key. You see how tables relate to each other.
  • Block 5 lists every non-clustered index with its key columns.
  • Block 6 lists tables that live outside the dbo schema.

Tips

  • Run the script on a copy of the database if you do not have read access to the live one.
  • Combine the result sets with the row counts to find orphaned tables.
  • Use the foreign key list to map relationships before you delete data.
  • Save the script to a shared folder so the whole team reuses one version.

Summary

  • One script shows the full schema of any SQL Server database.
  • The script returns one result table. Copy it in one action.
  • The script is read-only. It changes nothing.
  • The script works on every SQL Server version that has the sys views.
  • Save the script and reuse it on every project.

Leave a Reply

Your email address will not be published. Required fields are marked *