Extract Table Fields from SQL Database

Paopatsin/ August 15, 2020/ Database, MS SQL, Technology

Please read our Disclaimer

Works With
-Microsoft SQL Server 2016 (RTM) – 13.0.1601.5 (X64)

Although, you can generate table scripts easily using built-in context menu from SQL Database Management Studio, you may want to do it your own way.. the HARD way but FUN way. In this post, we will do that, we will extract the table definition of our database tables using SQL script. Deep down your SQL Database resides a view called INFORMATION_SCHEMA which contains details of your tables. This is just among the several ways you can do it. Let’s go!

Command:

SELECT 
    a.TABLE_NAME,
    STUFF(
        (SELECT ',' 
            + COLUMN_NAME 
            + ' ' 
            + DATA_TYPE 
            + CASE 
                WHEN CHARACTER_MAXIMUM_LENGTH IS NULL 
                THEN '' 
                ELSE '(' + CONVERT(NVARCHAR(255),CHARACTER_MAXIMUM_LENGTH) + ')' 
            END
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = A.TABLE_NAME
        FOR XML PATH('')),1,1,'') AS fields
FROM INFORMATION_SCHEMA.TABLES a
WHERE a.TABLE_TYPE = 'BASE TABLE' 
    AND A.TABLE_CATALOG = '[replace with your database name]'

Steps and Sample Usage:

  1. Open and log into your SQL Management Studio
  2. Open a New Query Windows or (CTRL+N)
  3. Run the following commands: (Copy and Paste the code block to your Query Window)

SELECT 
    a.TABLE_NAME,
    STUFF(
        (SELECT ',' 
            + COLUMN_NAME 
            + ' ' 
            + DATA_TYPE 
            + CASE 
                WHEN CHARACTER_MAXIMUM_LENGTH IS NULL 
                THEN '' 
                ELSE '(' + CONVERT(NVARCHAR(255),CHARACTER_MAXIMUM_LENGTH) + ')' 
            END
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = A.TABLE_NAME
        FOR XML PATH('')),1,1,'') AS fields
FROM INFORMATION_SCHEMA.TABLES a
WHERE a.TABLE_TYPE = 'BASE TABLE' 
    AND A.TABLE_CATALOG = 'AdventureWorks2016'
  1. Execute the query by pressing F5
Extract Table Fields from SQL Database
Extract Table Fields from SQL Database

Use Case:

  1. You want to create your own generate table script.

Caveat:

  1. This somewhat reinventing the wheel, however, what fun there is with a few clicks and following a few dialog boxes versus writing it by yourself and pulling some hair 🙂
  2. This will not work either if you don’t have read (SELECT) access to the source views (Information Schema).

Sample Data:
-AdventureWorks2016 Database

Share this Post