Geek Answers Handbook

SQL Server script to search for large object columns

A script search to scan all tables in all SQL Server databases and a list of columns that are large objects (TEXT, NTEXT, IMAGE VARCHAR (MAX), NVARCHAR (MAX), FILESTREAM, XML, VARBINARY).

while I can probably do it myself, I want a ready-made script.

+4
source share
2 answers
select * from information_schema.columns where data_type in ('TEXT', 'NTEXT','IMAGE' ,'XML', 'VARBINARY') or (data_type = 'VARCHAR' and character_maximum_length = -1) OR (data_type = 'NVARCHAR' and character_maximum_length = -1)

Refresh Removed FILESTREAM from IN since data_type is VARBINARY that is already written

+11
source

try it

 select * from information_schema.columns where DATA_TYPE in('text','ntext','xml','image') or (DATA_TYPE in('varchar','nvarchar','varbinary') and CHARACTER_MAXIMUM_LENGTH = -1) order by DATA_TYPE

filestream is stored as varbinary (max)

This will only capture varbinary (max), not varbinary (20), if you also want to then transfer varbinary to the first condition, for example, this

  select * from information_schema.columns where DATA_TYPE in('text','ntext','xml','image','varbinary') or (DATA_TYPE in('varchar','nvarchar') and CHARACTER_MAXIMUM_LENGTH = -1) order by DATA_TYPE
+3
source

More articles:


All Articles