CODEWITHSUNDEEP
sql-serversql-server-2005
webdad3
webdad3

Reputation: 9080

SQL Server 2005 find all columns in database for a certain datatype

Is there a way to query the system tables to find all the columns in a database that has a certain datatype.

For example if I needed to know the table name and the column where the datatype = ntext

Is there a way to do that?

Upvotes: 2

Views: 4686

Answers (4)

manji
manji

Reputation: 47978

SELECT so.name, sc.name
  FROM sys.objects so
  JOIN sys.columns sc ON so.object_id = sc.object_id
  JOIN sys.types stp ON sc.user_type_id = stp.user_type_id
                    AND stp.name = 'ntext'
 WHERE so.type = 'U' -- to show only user tables

Upvotes: 1

HPWD
HPWD

Reputation: 2240

I know this question has already been answered but I wanted to add the table name to the result set and this query does that.

SELECT a.name, o.name AS TableName, o.type, a.id, o.object_id, o.schema_id
FROM sys.syscolumns AS a INNER JOIN sys.systypes AS b ON a.xtype = b.xtype 
AND b.name = 'char' 
AND a.length = 6 INNER JOIN
sys.objects AS o ON a.id = o.object_id
WHERE (o.type = 'u') 
AND (o.schema_id = 1)

Upvotes: 1

Ash Burlaczenko
Ash Burlaczenko

Reputation: 25475

Try this

SELECT o.name AS 'Table Name', c.name AS 'Column Name' FROM sysobjects AS o
INNER JOIN syscolumns AS c ON o.name = c.object_id
INNER JOIN systypes AS t ON t.xtype = c.xtype
WHERE b.name = ' ntext'

Hope this helps.

Upvotes: 2

Chandu
Chandu

Reputation: 82933

Try this:

SELECT a.name -- OR a.* 
  FROM syscolumns a INNER JOIN systypes b
    ON a.xtype = b.xtype
   AND b.name = 'ntext' -- OR OTHER DATA TYPE.

Upvotes: 6

Related Questions