Query to find the LOB,varchar columns in the SQL database

For SQL server 2000 Instance : –

SELECT a.name as [ColumnName],B.name as [Table Name] , c.name
FROM sysCOLUMNS A , sysOBJECTS B ,systypes c
where A.id = B.id
and A.xtype in (35,34,241,99)
and A.xtype =c.xtype
AND B.xtype =’U’
AND B.name <>’dtproperties’
UNION
SELECT a.name as [ColumnName],B.name as [Table Name] , c.name
FROM sysCOLUMNS A , sysOBJECTS B , systypes c
where A.id = B.id
and A.xtype in (167,231,165)
and A.xtype =c.xtype
AND B.xtype =’U’
AND B.name <>’dtproperties’

For SQL server 2005 Instance : –

SELECT a.name as [ColumnName],B.name as [Table Name] , c.name
FROM SYS.COLUMNS A , SYS.OBJECTS B , sys.types c
where A.object_id = B.object_id
and A.system_type_id in (35,34,241,99)
and A.system_type_id =c.system_type_id
AND B.type =’U’
AND B.name <>’dtproperties’
UNION
SELECT a.name as [ColumnName],B.name as [Table Name] , c.name
FROM SYS.COLUMNS A , SYS.OBJECTS B , sys.types c
where A.object_id = B.object_id
and A.system_type_id in (167,231,165)
and A.system_type_id =c.system_type_id
AND B.type =’U’
AND B.name <>’dtproperties’

Advertisements

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