CODEWITHSUNDEEP
sqlsql-server-2008t-sql
ganders
ganders

Reputation: 7451

Script to find text datatypes in SQL Server

I have a few thousand tables in my SQL Server instance, and I need to start converted old deprecated datatypes into the new version (text to varchar(max)). Is it possible to script this out so I can just run select statement to find all of these without manually going through each of the tables in the database instance? TIA

Upvotes: 0

Views: 412

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280500

SELECT OBJECT_SCHEMA_NAME([object_id]),
  OBJECT_NAME([object_id]),
  name
FROM sys.columns
WHERE system_type_id IN (35,99);

You can even do this to generate a set of commands to change the tables:

SELECT 'ALTER TABLE ' 
  + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
  + '.' + QUOTENAME(OBJECT_NAME([object_id]))
  + ' ALTER COLUMN ' + QUOTENAME(name) 
  + ' ' + CASE system_type_id WHEN 99 THEN 'N' ELSE '' END
  + 'VARCHAR(MAX);'
FROM sys.columns
WHERE system_type_id IN (35,99);

Of course you'll also want to deal with nullability etc. but this is just a starting point.

Upvotes: 2

PraveenVenu
PraveenVenu

Reputation: 8337

try this

SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName,
    t.name, 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
WHERE t.name = 'text' 
ORDER BY c.OBJECT_ID;

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171529

You can use the INFORMATION_SCHEMA views for this.

Example 

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'text'

Upvotes: 1

Related Questions