5 years ago I wrote script SQL SERVER – 2005 – List All The Column With Specific Data Types, when I read it again, it is very much relevant and I liked it. This is one of the script which every developer would like to keep it handy. I have upgraded the script bit more. I have included few additional information which I believe I should have added from the beginning. It is difficult to visualize the final script when we are writing it first time. I use every script which I write on this blog, the matter of the fact, I write only those scripts here which I was using at that time. It is quite possible that as time passes by my needs are changing and I change my script. Here is the updated script of this subject. If there are any user data types, it will list the same as well.
SELECT s.name AS 'schema', ts.name AS TableName,
c.name AS column_name, c.column_id,
SCHEMA_NAME(t.schema_id) AS DatatypeSchema,
t.name AS Datatypename
,t.is_user_defined, t.is_assembly_type
,c.is_nullable, c.max_length, c.PRECISION,
c.scale
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id
INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.schemas s ON s.schema_id = ts.schema_id
ORDER BY s.name, ts.name, c.column_id
I would be very interested to see your script which lists all the columns of the database with data types. If I am missing something in my script, I will modify it based on your comment. This way this page will be a good bookmark for the future for all of us.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
Dear Pinal,
Its a nice script better if we can add server instance name and database name so it will be more illustrative.
Thanks
saqib
This is a database specific script.
Dear Pinal,
can you tell me any tool for business intelligence dashbooards which provide us a lot of variety of guages,charts and graphs etc for making executive dashboards and easily configurable with MS visual studio 2010 and C# ….
and freely available not licenced?
if i am not wrong,
for all the tables it is showing schema as “sys” because of incorrect join
“INNER JOIN sys.schemas s ON s.schema_id = t.schema_id”
it should be :
SELECT s.name AS ‘schema’, ts.name AS TableName,
c.name AS column_name, c.column_id,
SCHEMA_NAME(t.schema_id) AS DatatypeSchema,
t.name AS Datatypename
,t.is_user_defined, t.is_assembly_type
,c.is_nullable, c.max_length, c.PRECISION,
c.scale
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id
INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.schemas s ON s.schema_id = ts.schema_id
ORDER BY s.name, ts.name, c.column_id
Hi Akki,
The numbers of result was correct but the sys schema was getting incorrect result.
Thanks for bringing it to notice.
Kind Regards,
Pinal
Pinal,
Thanks for the Query, Pinal. Is there a reson why the provided schema views are never used in queries examples like yours? Many of the views have been avaliable since SQL7.0 and I use them all the time but I rarly see them referenced. For Example:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS;
–or
SELECT * from sys.all_columns;
Thanks
mycow
Can you please tell that how to get information regarding whether the column is identity and its seed and increment value? Also default value of the column.
Is There any way to fetch all default constraints created on BIT (datatype )column only with in a database.?
please suggest a solution.
like we have to fetch all BIT columns and default constraint on this column if there is any within a database.