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)

, ,
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

  • 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

    Reply
  • Wilson Gunanithi . J
    August 10, 2007 7:36 am

    Hi Atul,

    You have done a good thing… Congrats..

    Regards,
    Wilson Gunanithi.J

    Reply
  • @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 ( https://blog.sqlauthority.com/ )

    Reply
  • @Jim Mabey,

    I can not agree with you any more.

    Thanks,

    Regards,
    Pinal Dave ( https://blog.sqlauthority.com/ )

    Reply
    • Sandesh,

      It is ok Pinal, We are here to share our knowledge and grow…thank you so much for your highly useful posts always.

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

    Reply
  • It was more than usefull for me, thx alot

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

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

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

    Reply
  • Need to know the best practices for testing Database schema.

    Reply
  • Hmmm, information_schema.columns displays the view columns.

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

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

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

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

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

    Reply
  • Very handy script. This one goes strait to my favourites list.
    Is it possible to apply the same concept to User Stored Procedures and User Defined Functions?
    That would also be very nice.

    Reply
  • Joaquim Félix,

    Do you want to know the parameters or the scripts?

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

    Reply
  • How to get the x and y value as the length value entered in nVarChar(x) and numeric(x,y). Thanks

    Reply

Leave a Reply