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 is SQLServer MVP, DBA, Speaker, Trainer, Blogger and Community Lead. You can find him speaking at a local UG Event or a SQL Webcast. He has 8+ years of Experience and worked with Industry Leaders like Wipro, HP and HCL. He has many SQL Certifications under his belt. His core competency lies in administration of SQL Server. Always ready to help, online \offline. His life’s mantra is “Knowledge Increases by sharing so, Pass it on”. To know about his speaking engagements visit: here...
Follow Sarab on
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_identityfrom 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.
Leave a comment