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)
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.