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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

4 comments:

Bret Ferrier said...: 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.; August 11, 2009 at 9:40 AM
Unknown said...: You can also use INFORMATION_SCHEMA views, which give you a ton of information with a very simple query:

SELECT *
FROM information_schema.columns; August 11, 2009 at 9:54 AM
Suprotim Agarwal said...: Blog.Runxc: Thanks. I will check it out

jasonPenny: Yes the information_schema.columns is very handy!; August 12, 2009 at 6:02 AM
Anonymous said...: Information_schema is good thing to rely on as sys.* might change/become inaccessible.
Govind; August 13, 2009 at 10:26 AM