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.
Regards
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
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,
is_nullable,is_identity
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.
thanks sdillon for the inputs. Smile
thanks sdillon for the inputs. 🙂