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 (http://blog.SQLAuthority.com)

About these ads

50 thoughts on “SQL SERVER – 2005 – List All The Column With Specific Data Types

  1. Hi
    we should not touch the underline table as per microsoft statement. therefore an easy and cheapest way to get the information about the column is by using the information_schema view therefore the above task can be done in this manner

    DECLARE @DataType VARCHAR(50)
    SELECT @DataType = ‘TEXT’
    SELECT * FROM Information_Schema.Columns WHERE Data_Type = @DataType

  2. @Atul,

    sys.columns and sys.types are not underline tables. They are views to protect underline tables. Your information is incorrect.
    information_schema is undocumented where as Microsoft recommends to use sys.columns and sys.types or any other sys. views.

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

  3. It never ceases to amaze me how much energy people will put into trying to fault someone elses work, when instead, it seems to me, they should be trying to do something of their own.

  4. This might a little too late to add another comment to this blog. Anyways better late than never

    Jim/Pinal

    Why do you have to think that when someone comments on something that you have mentioned, it only means that their only intention is to find fault with you?

    It is good that Atul has come forward with another point to the same discussion. Now we all know about these 2 views.

  5. The problem I am having with this wonderful script is it does not go down to View level ie it does not list views – is there a fix to that ?

    Thanks

  6. hi, could u please let me know that how can we find the duplicate columns in aprox 30 tables of same schema. based on name.

  7. Hello Sir,
    I need some help.
    Is there any procedure that can alter my columns in the database from chanr to nchar,varchar to nvarchar and text to ntext
    Thanks in advance

  8. Hi All

    i have a column which is a computed column (when click on Table+ look in column DataType) It showes me Computed. I have no of such columns in my database.
    With a SQL statement how do i get list of such columns.

    Thanks

  9. HELLO

    Is there any procedure that can alter all my columns in the database from int to nvarchar and float to nvarchar
    Thanks in advance

  10. need some help in Sqlserver.
    I need a script file to change table column datatype from int to bigint when the two tables are associated with foreign key , I try to disable the foreign key in the child table and they try to alter but it did not work

  11. SELECT SysObjects.[Name] as TableName,
    SysColumns.[Name] as ColumnName,
    SysTypes.[Name] As DataType,
    SysColumns.[Length] As Length
    FROM
    SysObjects INNER JOIN SysColumns
    ON SysObjects.[Id] = SysColumns.[Id]
    INNER JOIN SysTypes
    ON SysTypes.[xtype] = SysColumns.[xtype]
    WHERE SysObjects.[type] = ‘U’
    ORDER BY SysObjects.[Name]

  12. @Madhivanan

    Madhivanan sorry for the late reply.
    My first idea was, similarly to this post, to determine the User Stored Procedures (USP) and User Defined Functions (UDF) that had a specific data type in it’s parameters.
    However, after reading your post, the same idea applied to the variables used inside the code would also be nice, since the main purpose is to replace old or deprecated data types with new or enhanced ones.
    Regards.

  13. 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;

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

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

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

  17. 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’

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

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

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

  21. 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;

    • 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’,”),’ ‘,”)

  22. Pingback: SQL SERVER – 2012 – List All The Column With Specific Data Types in Database « SQL Server Journey with SQL Authority

  23. 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;

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

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

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