May 17, 2016 at 9:44 pm
I need to find all table names in all databases with data types text, ntext and image. I came up with the following query however it returns a NULL value for the Table Name:
CREATE TABLE #DBDATATYPES
(
DbName nvarchar(255)
,TableName nvarchar (255)
,ColumnName nvarchar (255)
,Datatype nvarchar (255)
)
INSERT INTO #DBDATATYPES
exec sp_msforeachdb 'select "?" AS DatabaseName, OBJECT_NAME(c.OBJECT_ID) as TableName, c.name ColumnName, t.name
FROM [?].sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name IN (''text'', ''ntext'', ''image'')
ORDER BY c.OBJECT_ID'
select * from #DBDATATYPES
order by DbName
DROP table #DBDATATYPES
May 17, 2016 at 10:15 pm
deep_trance_ (5/17/2016)
exec sp_msforeachdb 'select "?" AS DatabaseName, OBJECT_NAME(c.OBJECT_ID, DB_ID(''?'')) as TableName,
_____________
Code for TallyGenerator
May 18, 2016 at 10:57 am
IF OBJECT_ID('tempdb.dbo.#DBDATATYPES') IS NOT NULL
DROP TABLE #DBDATATYPES
CREATE TABLE #DBDATATYPES
(
DbName nvarchar(128)
,TableName nvarchar (128)
,ColumnName nvarchar (128)
,Datatype nvarchar (128)
)
INSERT INTO #DBDATATYPES
EXEC sys.sp_MSforeachdb '
IF ''?'' IN (''master'', ''msdb'')
RETURN;
USE [?]
SELECT ''?'' AS DatabaseName, OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName, t.name
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN (''image'', ''ntext'', ''text'')
ORDER BY TableName, ColumnName'
SELECT * FROM #DBDATATYPES
ORDER BY DbName, TableName, ColumnName
DROP TABLE #DBDATATYPES
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply