TSQL – show all tables and columns with is nullable and is primary key

Here is a small query to help you get a list of all tables and columns in your database, showing whether each column is nullable or not, and showing whether each column is part of the primary key or not.

Enjoy!


SELECT col.TABLE_SCHEMA, col.TABLE_NAME, col.COLUMN_NAME, ORDINAL_POSITION,

        COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,

        NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,

        DATETIME_PRECISION, is_nullable,

       CASE WHEN keycol.primary_key = col.COLUMN_NAME THEN 'Yes' ELSE 'No' END AS is_primarykey

 FROM INFORMATION_SCHEMA.COLUMNS col

 LEFT JOIN

 (

select pk.TABLE_SCHEMA, pk.TABLE_NAME, column_name as 'primary_key'

from information_schema.table_constraints pk

      inner join information_schema.key_column_usage c on c.table_name = pk.table_name

            and c.constraint_name = pk.constraint_name

where constraint_type = 'primary key'

) keycol

      ON col.TABLE_SCHEMA = keycol.TABLE_SCHEMA

      AND col.TABLE_NAME = keycol.TABLE_NAME   

      AND col.COLUMN_NAME = keycol.primary_key

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s