SQL SERVER – 2005 – List All The Column With Specific Data Types

Since we upgraded to SQL Server 2005 from SQL Server 2000, we have used following script to find out columns with specific datatypes many times. It is very handy small script.

SQL Server 2005 has new datatype of VARCHAR(MAX), we decided to change all our TEXT datatype columns to VARCHAR(MAX). The reason to do that as TEXT datatype will be deprecated in future version of SQL Server and VARCHAR(MAX) is superior to TEXT datatype in features. We run following script to identify all the columns which are TEXT datatype and developer converts them to VARCHAR(MAX)

Script 1 : Simple script to identify all the columns with datatype TEXT in specific database
SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'text' --you can change text to other datatypes
ORDER BY c.OBJECT_ID;
GO

Script 2 : Extended script to identify all the columns datatype and other information in specific database
SELECT
OBJECT_NAME(c.OBJECT_ID) TableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.PRECISION
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
ORDER BY c.OBJECT_ID;

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – 2005 – SSMS – Enable Autogrowth Database Property
Next Post
SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date

Related Posts

65 Comments. Leave new

  • I have a question,

    In oracle, I can define variable’s datatype using another table’s column datatype in procedure or function.. (see the example) can you help me find it out similar things in sql server 2005? Thanks in an advance.

    Example:

    eno in emp.empno%type,
    name out emp.ename%type,
    job out emp.job%type,
    salary out emp.sal%type,
    location out dept.loc%type

    create or replace procedure display (
    eno in emp.empno%type,
    name out emp.ename%type,
    job out emp.job%type,
    salary out emp.sal%type,
    location out dept.loc%type
    )
    is
    begin
    select ename,job,sal,loc into name,job,salary,location from emp e,dept d
    where e.deptno=d.deptno AND empno=eno;
    end;

    Reply
  • what is the datatype for email in sql 2005???

    Reply
  • Remisha – Thank you for the query, it’s really handy.

    Reply
  • Staffan Hedström
    August 25, 2010 2:34 pm

    Thanks for the script, you saved me an hour!

    Reply
  • Hi,
    In addition to this script can i add another column that states what exactly is the data length (not declared length), that means concatenating MAX( DataLength(ColumnName)) function

    Reply
  • I wanted to find out what the column data types were for the view sys.databases.

    To return the column data types for this view, use Pinal’s Script 2 above, but query “sys.all_columns” instead of “sys.columns”.

    To return only the columns for sys.databases, add:

    WHERE object_name(c.OBJECT_ID) = ‘sysdatabases’

    before the ORDER BY.

    Reply
  • Hi Guys,

    nice post, I want a result in the following format

    TableName
    ———-

    columnName datatype Length
    test1 int 4

    TableName
    —————
    columnName datatype Length
    test2 int 4

    Reply
    • That format matters only if you want to show them in a front end application. Why do you want to have data in such format?

      Reply
  • Hi Guys,
    Have any idea to alter the system data type.

    Exp:
    UPDATE INFORMATION_SCHEMA.COLUMNS SET NUMERIC_PRECISION=18,NUMERIC_SCALE=2
    WHERE DATA_TYPE = ‘decimal’

    Reply
  • i want Search text in any Column of Specify Table ,

    I want that text which Column ::

    How do I

    Reply
  • Rajib Pradhan
    March 5, 2012 2:53 pm

    Hi Pinal,
    I want a script which will find all the data types used by all the primary keys of the database. That means list all the primary keys with their data types in a database.
    Will you please write a script for this.

    Thanks,
    Rajib

    Reply
  • I was actually lookiing for a way to list the columns and datatypes for User Defined Table Types. This article helped but I still had to do a lot of research before I came up with this:

    and was able to do this:

    SELECT s.name as ‘schema’
    , tt.name as TableName
    , c.name AS column_name
    ,c.column_id
    ,SCHEMA_NAME(t.schema_id) AS type_schema
    ,t.name AS type_name
    ,t.is_user_defined
    ,t.is_assembly_type
    ,c.max_length
    ,c.precision
    ,c.scale
    ,t.user_type_id
    ,c.object_id
    FROM sys.columns AS c
    JOIN sys.types AS t ON c.user_type_id=t.user_type_id
    JOIN sys.table_types tt ON tt.type_table_object_id = c.object_id
    JOIN sys.schemas s ON s.schema_id = tt.schema_id
    Order by s.name,tt.name, c.column_id

    Reply
  • Rajib Pradhan
    April 5, 2012 9:45 am

    Thanks Ron,
    I think your script will list all the columns with data types. I want to refine my requirement that list all the primary key columns with data type.

    Rajib

    Reply
  • Thank u very much…………..

    Reply
  • Good One !!
    Have added the NULLABLE condtion for each column!!

    SELECT
    OBJECT_NAME(c.OBJECT_ID) TableName
    ,c.name AS ColumnName
    ,SCHEMA_NAME(t.schema_id) AS SchemaName
    ,t.name AS TypeName
    ,t.is_user_defined
    ,t.is_assembly_type
    ,c.max_length
    ,c.PRECISION
    ,c.scale
    ,’NULLABLE’ = case when c.is_nullable=’true’ then ‘YES’ else ‘NO’ end
    FROM sys.columns AS c
    JOIN sys.types AS t ON c.user_type_id=t.user_type_id
    ORDER BY c.OBJECT_ID;

    Reply
  • Nice ya. Found your valuable effort here.
    Thanks for this.
    Ashok.

    Reply
  • hi i want select a date from a filde
    if fdat=2012/10/10
    i want 20121010
    can help me !!!

    Reply
    • Hope this will help you ..

      select replace(replace(replace(convert(char(4),YEAR(‘2012/10/10’))+’A’+convert(char(4),MONTH(‘2012/10/10’))+’B’+convert(char(4),DAY(‘2012/10/10′)),’A’,”),’B’,”),’ ‘,”)

      Reply
    • SELECT REPLACE(‘2012/10/10’, ‘/’, ”)

      –Eg:-
      SELECT REPLACE(FieldName, ‘/’, ”) FROM TableName

      Reply
  • thanx a lot buddy

    Reply
  • Except manually , Can i migrate the data belonging to one schema to a different one in same db. Please tell me ASAP.

    Reply
  • Can you please explain what the single characters ‘c’, ‘t’ mean below? It’s a copy from this blog.

    SELECT
    OBJECT_NAME(c.OBJECT_ID) TableName
    ,c.name AS ColumnName
    ,SCHEMA_NAME(t.schema_id) AS SchemaName
    ,t.name AS TypeName
    ,t.is_user_defined
    ,t.is_assembly_type
    ,c.max_length
    ,c.PRECISION
    ,c.scale
    FROM sys.columns AS c
    JOIN sys.types AS t ON c.user_type_id=t.user_type_id
    ORDER BY c.OBJECT_ID;

    Reply
    • c and t are the aliases for sys.columns and sys.types respectively. You can see that name asighed in the last 2nd and 3rd row from the bottom.

      Reply
  • Syed Sabah Hassan
    January 13, 2015 8:05 am

    Hi Pinal, Quite helpful article
    Sabah

    Reply

Leave a Reply

Menu