SQL SERVER - 2005 - List All The Column With Specific Data Types « Journey to SQL Authority with Pinal Dave

Feed on: Posts; Comments

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

August 9, 2007 by pinaldave

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://www.SQLAuthority.com)

Posted in DBA, Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology | 13 Comments

13 Responses to “SQL SERVER - 2005 - List All The Column With Specific Data Types”

on August 9, 2007 at 9:34 am Atul

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
on August 10, 2007 at 7:36 am Wilson Gunanithi . J

Hi Atul,

You have done a good thing… Congrats..

Regards,
Wilson Gunanithi.J
on August 25, 2007 at 8:25 pm pinaldave

@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 )
on September 13, 2007 at 9:57 pm Jim Mabey

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.
on September 13, 2007 at 10:00 pm pinaldave

@Jim Mabey,

I can not agree with you any more.

Thanks,

Regards,
Pinal Dave ( http://www.SQLAuthority.com )
on December 16, 2007 at 5:09 pm mandana

It was more than usefull for me, thx alot
on April 25, 2008 at 10:21 pm Arvind

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.
on May 28, 2008 at 12:56 am rem

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
on June 6, 2008 at 10:40 am Puneet

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.
on June 6, 2008 at 10:42 am Puneet

Need to know the best practices for testing Database schema.
on October 3, 2008 at 1:19 am Joe

Hmmm, information_schema.columns displays the view columns.
on December 10, 2008 at 10:02 pm Naren Sangu

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
on January 3, 2009 at 2:49 pm vineet

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