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


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

4 comments:

Blog.Runxc 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.

jasonpenny 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

Suprotim Agarwal said...

Blog.Runxc: Thanks. I will check it out

jasonPenny: Yes the information_schema.columns is very handy!

Anonymous said...

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