January 30, 2018 at 3:26 am
Hi,
Is there any way that to find out all the tables which include a VARBINARY type column?
Thanks,
January 30, 2018 at 3:34 am
Expand the tables to search that one by one.
January 30, 2018 at 3:38 am
Quick suggestion
😎USE TEEST;
GO
SET NOCOUNT ON;
SELECT
OBJECT_SCHEMA_NAME(SCOL.object_id) AS SCHEMANAME
,OBJECT_NAME(SCOL.object_id) AS TABLENAME
,SCOL.name AS COLUMNNAME
FROM sys.columns SCOL
INNER JOIN sys.types STYPE
ON SCOL.system_type_id = STYPE.system_type_id
WHERE STYPE.name = N'VARBINARY'
AND SCOL.max_length = -1
AND OBJECT_SCHEMA_NAME(SCOL.object_id) <> N'sys';
January 30, 2018 at 3:56 am
Slightly different to Eirikur's:
USE [YourDatabase];
SELECT s.[name] AS SchemaName, t.[name] AS TableName, c.[name] AS ColumnName
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types ct on c.user_type_id = ct.user_type_id
WHERE ct.[name] = N'varbinary'
AND s.[name] != N'sys' --Ignore System objects
AND t.[name] != N'sysdiagrams'; --Ignore the sysdiagrams, if it's been created
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 30, 2018 at 3:59 am
Or even
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'varbinary'
...
January 30, 2018 at 10:09 am
SELECT SCHEMA_NAME(t.schema_id) AS schema_name,
t.name AS table_name,
c.name AS column_name,
c.max_length,
c.is_nullable,
c.is_computed
FROM sys.columns c
INNER JOIN sys.tables t ON t.object_id = c.object_id
WHERE
c.user_type_id IN (SELECT t.user_type_id FROM sys.types t WHERE t.name IN ('varbinary')) AND
t.is_ms_shipped = 0
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply