SQL Server script to find LOB columns
Looking for a script to scan all tables in all SQL Server databases and 开发者_如何转开发list the columns which are large objects (TEXT, NTEXT, ,IMAGE VARCHAR(MAX), NVARCHAR(MAX), FILESTREAM, XML, VARBINARY).
while I probably can code this myself, I want a ready made script.
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)
Update Removed FILESTREAM from IN since the data_type is VARBINARY which is already captured
Try this
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) for example, if you also want that then move varbinary to the first condition, like 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
select t.table_schema,
t.table_name,
c.COLUMN_NAME,c.data_type
from information_schema.columns c
inner join INFORMATION_SCHEMA.tables t
on c.TABLE_SCHEMA = t.TABLE_SCHEMA
and c.TABLE_NAME = t.TABLE_NAME
where t.TABLE_TYPE = 'BASE TABLE'
and ((c.data_type in ('VARCHAR', 'NVARCHAR') and c.character_maximum_length = -1)
or data_type in ('TEXT', 'NTEXT', 'IMAGE', 'VARBINARY', 'XML', 'FILESTREAM'))
group by t.table_schema,
t.table_name, c.COLUMN_NAME,c.data_type
order by t.table_schema,
t.table_name
精彩评论