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

Solarwinds

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)

Solarwinds
,
Previous Post
SQL SERVER – Advanced Data Quality Services with Melissa Data – Azure Data Market
Next Post
SQL SERVER – Find First Non-Numeric Character from String

Related Posts

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

    Reply
    • This is a database specific script.

      Reply
      • 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 :)

  • 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

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

      Reply
      • I cannot find ANYWHERE how to convert BYTES into a useful number. When you create a table, you use VARCHAR(50) OR whatever, not bytes. So how is that doable using this query?

  • 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

    Reply
  • 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.

    Reply
  • 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.

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

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

    Reply
  • Thanks!

    Reply
  • 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)

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

    Reply
  • Thank you so much for this script. very simple , understandable script :)

    Reply
  • Thanks much and please help me to get values along with.

    Reply
  • Fantastic script! Thanks so much

    Reply
  • O Patel,
    you are a genious.. last surviving genious

    Reply

Leave a Reply

Menu