SQL Server: How to find column related detailed info for any table?

If i ask you to get me a list of all the columns in 25 tables those are being replicated or being used by certain application module, how will you get this info?

I know most of you will opt for getting the info manually, but if this becomes a routine activity and the column numbers keep on increasing will you still opt for manual effort?

I have an interesting way to get this info which is easy to use and provides much more info, this will not only get you name for all the columns for a table but it can also provide information regarding nullability, type, max_size etc all such info you can think of. By this way you can also get the column info for all the tables in a Database.

All you need to do is use sys.columns Object Catalog view. This view returns a row for each column of an object that has columns, such as views or tables. The following is a list of object types that have columns:

Table-valued assembly functions (FT)

Inline table-valued SQL functions (IF)

Internal tables (IT)

System tables (S)

Table-valued SQL functions (TF)

User tables (U)

Views (V)

To test this i’ve created on table and used the same query to get the desired output.

select object_id, name, system_type_id, max_length, 
from sys.columns where object_id = object_id('test2')

after using the above mentioned query i get an output similar to this.



Hope this will help you in some way or the other.



Sarabpreet Anand

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook


3 Comments on “SQL Server: How to find column related detailed info for any table?”

  1. Hi Sarab,

    You can also automate this further by using thebuilt-in sproc “sp_msforeachtable” to iterate over each table in a database. See the example below.

    sp_msforeachtable ‘select object_id, name, system_type_id, max_length,
    from sys.columns’

    This will yield the same output but for all tables and it automatically groups them into an easy to read format. If you place a join to sys.objects in your query, it will also display the table name similiar to how Information_Schema.Columns does.

Leave a Reply

Your email address will not be published.