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.

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