uchRsqm8

1CREATE TABLE #LIst_DB (name nvarchar(128))
2
3INSERT INTO #LIst_DB 
4
5select name from  sys.databases
6WHERE database_id > 4
7AND state = 0;
8
9select * from #LIst_DB
10
11DROP TABLE #LIst_DB
12	
13DECLARE @SQL NVARCHAR(max)
14
15SET @SQL = stuff((
16            SELECT '
17UNION
18SELECT ' + quotename(NAME, '''') + ' as Db_Name, Name collate SQL_Latin1_General_CP1_CI_AS as Table_Name
19FROM ' + quotename(NAME) + '.sys.tables WHERE NAME =  @TableName '
20            FROM sys.databases
21            ORDER BY NAME
22            FOR XML PATH('')
23                ,type
24            ).value('.', 'nvarchar(max)'), 1, 8, '')
25
26--PRINT @SQL;
27
28EXECUTE sp_executeSQL @SQL
29    ,N'@TableName varchar(30)'
30    ,@TableName = 'AWBuildVersion'
31	
32-- The table to find
33DECLARE @find nvarchar(257) = N'dbo.AWBuildVersion';
34
35-- Holds results
36DECLARE @results table 
37(
38    [Db_Name] sysname PRIMARY KEY,
39    [Database Version] nvarchar(25) NOT NULL
40);
41
42-- Current database   
43DECLARE @db sysname;
44
45-- AdventureWorks databases cursor
46DECLARE dbs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
47SELECT D.[name] 
48FROM sys.databases AS D
49WHERE 
50    D.[name] LIKE N'AdventureWorks%'
51    AND D.state_desc = N'ONLINE'
52    AND DATABASEPROPERTYEX(D.[name], 'Collation') IS NOT NULL
53ORDER BY
54    D.[name];
55
56OPEN dbs;
57
58WHILE 1 = 1
59BEGIN
60    -- Next database
61    FETCH dbs INTO @db;
62    IF @@FETCH_STATUS = -1 BREAK;
63
64    -- Find [Database Version] if @find table exists
65    INSERT @results ([Db_Name], [Database Version])
66    EXECUTE sys.sp_executesql
67        N'
68            -- Construct 3-part object name
69            DECLARE @object nvarchar(386) = 
70                QUOTENAME(@db) + N''.'' +
71                ISNULL(QUOTENAME(PARSENAME(@find, 2)), '''') + N''.'' +
72                QUOTENAME(PARSENAME(@find, 1));
73
74            -- Query to find [Database Version]
75            DECLARE @sql nvarchar(max) = N''
76                SELECT 
77                    Db_Name = '''''' + @db + ''''''
78                    [Database Version] 
79                FROM '' + @object;
80
81            IF OBJECT_ID(@object, N''U'') IS NOT NULL
82                EXECUTE (@sql)',
83        N'@db sysname, @find nvarchar(257)',
84        @db = @db, @find = @find;
85END;
86
87CLOSE dbs; DEALLOCATE dbs;
88
89SELECT
90    R.[Db_Name],
91    [Table_Name] = @find,
92    R.[Database Version]
93FROM @results AS R
94ORDER BY
95    R.[Db_Name];
96	
97EXEC dbo.sp_foreachDB @command1 = 'select [Database Version] from dbo.AWBuildVersion', 
98                 @name_pattern = 'AdventureWorks%'