August 11, 2009

List down the DataType and Size Of all Columns of All Tables in a SQL Server 2005/2008 Database




While programming a Data Layer, I often refer to the database and its tables for the datatypes and its sizes. It’s real handy to be able to print a list of the properties of the columns. Here’s a query that will help you fetch the DataType and Size Of all Columns of All Tables in a SQL Server 2005/2008 Database.

The following query fetches the Schema, DataTypes and Sizes of all columns of all tables in the AdventureWorks database

USE AdventureWorks;
GO
SELECT
OBJECT_NAME
(col.OBJECT_ID) as [TableName],
col.name AS [ColName],
SCHEMA_NAME(typ.schema_id) AS type_schema,
typ.name AS type_name,
col.max_length
FROM sys.columns AS col
JOIN sys.tables AS tbl on col.object_id = tbl.object_id
JOIN sys.types AS typ ON col.user_type_id = typ.user_type_id
ORDER BY [TableName];
GO

OUTPUT

image


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

4 Responses to "List down the DataType and Size Of all Columns of All Tables in a SQL Server 2005/2008 Database"
  1. Blog.Runxc said...
    August 11, 2009 at 9:40 AM

    If you take a look at the SubSonic project they are doing just what you are talking about and more. They are also grabbing the namespace, whether or not it is nullable, if it is a foreign key, primary key etc. If you look at the different data providers you can see how they are doing it for SqlServer,Oracle,SQLite,MySQL etc.

  2. jasonpenny said...
    August 11, 2009 at 9:54 AM

    You can also use INFORMATION_SCHEMA views, which give you a ton of information with a very simple query:

    SELECT *
    FROM information_schema.columns

  3. Suprotim Agarwal said...
    August 12, 2009 at 6:02 AM

    Blog.Runxc: Thanks. I will check it out

    jasonPenny: Yes the information_schema.columns is very handy!

  4. Anonymous said...
    August 13, 2009 at 10:26 AM

    Information_schema is good thing to rely on as sys.* might change/become inaccessible.
    Govind

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions