SQL SERVER – 2012 – List All The Column With Specific Data Types in Database

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.

Click to Download Scripts

Reference : Pinal Dave (http://blog.SQLAuthority.com)

 

About these ads

13 thoughts on “SQL SERVER – 2012 – List All The Column With Specific Data Types in Database

  1. 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

      • 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?

      • sir i’ve a question and need needful from yo :)
        A column in table can have any datatype and it can contain any data(in case varchar).
        I want to get the column data’s data type based on the values we’ve stored in that column.
        Example: A column with varchar data type can contain integer data. so based on integer values i want to get data type as int or float or etc…not as varchar :)

  2. 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

  3. 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

  4. 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.

  5. 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

  6. 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

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #051 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s