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, 
is_nullable,is_identity 
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.

Thanks,

Sarabpreet Singh