Get Detailed Table and Column Information in SQL


There is often some basic information that you want to see regarding the tables and columns in your current database. You don’t want to fuss with the INFORMATION_SCHEMA or SYS views to join it all together either. So, I decided to spend a few minutes to get together the things that I use the most into a single query and post it here for everyone.

Select          t.TABLE_NAME As [Table],
                c.COLUMN_NAME As [Column],
                c.DATA_TYPE As [DataType],
                c.CHARACTER_MAXIMUM_LENGTH As [Length],
                Case
                    When c.IS_NULLABLE = 'Yes'
                    Then 1
                    Else 0
                End As [Nullable],
                Left(k.CONSTRAINT_NAME, 2) As [KeyType],
                d.Description As [MS_Description]
From            INFORMATION_SCHEMA.TABLES t
Inner Join      INFORMATION_SCHEMA.COLUMNS c
On              t.TABLE_NAME = c.TABLE_NAME
Left Outer Join INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
On              t.TABLE_NAME = k.TABLE_NAME
And             c.COLUMN_NAME = k.COLUMN_NAME
Left Outer Join (Select    o.Name As [Table],
                           c.name As [Column],
                           ep.value As [Description]
                From       sys.objects o
                Inner Join sys.extended_properties ep
                On         o.object_id = ep.major_id
                LEFT Join  syscolumns c
                On         ep.minor_id = c.colid
                And        ep.major_id = c.id
                Where      o.type = 'U') As d
On              t.TABLE_NAME = d.[Table]
And             c.COLUMN_NAME = d.[Column]
Where           t.TABLE_TYPE = 'BASE TABLE'
And             t.TABLE_NAME
Not In          ('sysdiagrams')
Order By        t.TABLE_NAME,
                c.ORDINAL_POSITION

I hope this saves everyone else as much time as it saves me! It also includes the MS_Description schema property in case you document your databases in hopes that .NET will support a self-documenting database architecture some day.

If you have any questions or comments on how I can make this script better, please feel free to comment on this post.

Advertisements

2 thoughts on “Get Detailed Table and Column Information in SQL

  1. Hello Nathan, I am writing regarding your thread on CreatedBy,CreatedDate in Entity frame, I followed your instruction the Datetime issue is resolve, however, (suser_sname() is storing “NativeSqlUser” any idea how to solve it.

    1. The suser_sname() function will return the user currently logged in to the SQL server. Chances are your application is using one account in the connection string to authenticate with the SQL server. If you instead turn on integrated security between your application and SQL, it should use the user you’re currently authenticating with as the username for SQL. However, depending on how you’re doing authentication in your application, it could also not be the true user. Also, if you’re going to use this type of “pass-through” authentication, you’ll need each user to have access to the database. There are some trade offs to be made between one account and managing accounts through SQL.

      I just made an Active Directory domain group for each level of access, then added those groups to my application and the database with their appropriate permissions. That way I don’t have to maintain them in both places (App & SQL). So, you just have to make a decision on whether you want to authenticate a single user or go through the trouble of changing your app authentication, connection string (to add Integrated Security=True;), setup the user accounts in SQL, etc.

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