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 (https://blog.sqlauthority.com)
19 Comments. Leave new
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
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
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.
Hi sir,
I am getting the result…but result window contains separate results for each table
DECLARE @tablename varchar(50)
DECLARE @table CURSOR
SET @table = CURSOR FOR
SELECT name FROM SYS.TABLES
OPEN @table
FETCH NEXT
FROM @table INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
exec SP_COLUMNS @tablename
FETCH NEXT
FROM @table INTO @tablename
END
CLOSE @table
DEALLOCATE @table
Best Regards
Ashwini
SELECT ta .name AS tablename,
SCHEMA_NAME(ta.schema_id) AS schema_name,
c.name AS column_name,
t.name AS Datatypename,c.max_length, c.PRECISION,
c.scale,c.is_nullable,t.is_user_defined, t.is_assembly_type
FROM sys.tables AS ta
INNER JOIN sys.columns c ON ta.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id
ORDER BY schema_name
Thanks!
Hi Pinal,
Is there any way to get the original columns and corresponding tables used for creating a view (eg, if col1 from table1 is used as EmpNo in view I want col1 from table1 as result, not EmpNo)
I use this:
SELECT [Table_Name],[column_name],[ordinal_position],[IS_NULLABLE],[data_type],[character_maximum_length]
FROM INFORMATION_SCHEMA.COLUMNS
where table_name in ()
in order to lists all the columns of the database with data types. Regards BO
Thank you so much for this script. very simple , understandable script :)
Thanks much and please help me to get values along with.
Fantastic script! Thanks so much
O Patel,
you are a genious.. last surviving genious