|
|
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
Did you like this post?
|
|
||
|
|
|
|
|
subscribe via rss |
|
subscribe via e-mail |
|
print this post |
|
follow me on twitter |
comments
4 Responses to "List down the DataType and Size Of all Columns of All Tables in a SQL Server 2005/2008 Database"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.
You can also use INFORMATION_SCHEMA views, which give you a ton of information with a very simple query:
SELECT *
FROM information_schema.columns
Blog.Runxc: Thanks. I will check it out
jasonPenny: Yes the information_schema.columns is very handy!
Information_schema is good thing to rely on as sys.* might change/become inaccessible.
Govind
Post a Comment