Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE #LIst_DB (name nvarchar(128))
- INSERT INTO #LIst_DB
- select name from sys.databases
- WHERE database_id > 4
- AND state = 0;
- select * from #LIst_DB
- DROP TABLE #LIst_DB
- DECLARE @SQL NVARCHAR(max)
- SET @SQL = stuff((
- SELECT '
- UNION
- SELECT ' + quotename(NAME, '''') + ' as Db_Name, Name collate SQL_Latin1_General_CP1_CI_AS as Table_Name
- FROM ' + quotename(NAME) + '.sys.tables WHERE NAME = @TableName '
- FROM sys.databases
- ORDER BY NAME
- FOR XML PATH('')
- ,type
- ).value('.', 'nvarchar(max)'), 1, 8, '')
- --PRINT @SQL;
- EXECUTE sp_executeSQL @SQL
- ,N'@TableName varchar(30)'
- ,@TableName = 'AWBuildVersion'
- -- The table to find
- DECLARE @find nvarchar(257) = N'dbo.AWBuildVersion';
- -- Holds results
- DECLARE @results table
- (
- [Db_Name] sysname PRIMARY KEY,
- [Database Version] nvarchar(25) NOT NULL
- );
- -- Current database
- DECLARE @db sysname;
- -- AdventureWorks databases cursor
- DECLARE dbs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
- SELECT D.[name]
- FROM sys.databases AS D
- WHERE
- D.[name] LIKE N'AdventureWorks%'
- AND D.state_desc = N'ONLINE'
- AND DATABASEPROPERTYEX(D.[name], 'Collation') IS NOT NULL
- ORDER BY
- D.[name];
- OPEN dbs;
- WHILE 1 = 1
- BEGIN
- -- Next database
- FETCH dbs INTO @db;
- IF @@FETCH_STATUS = -1 BREAK;
- -- Find [Database Version] if @find table exists
- INSERT @results ([Db_Name], [Database Version])
- EXECUTE sys.sp_executesql
- N'
- -- Construct 3-part object name
- DECLARE @object nvarchar(386) =
- QUOTENAME(@db) + N''.'' +
- ISNULL(QUOTENAME(PARSENAME(@find, 2)), '''') + N''.'' +
- QUOTENAME(PARSENAME(@find, 1));
- -- Query to find [Database Version]
- DECLARE @sql nvarchar(max) = N''
- SELECT
- Db_Name = '''''' + @db + ''''''
- [Database Version]
- FROM '' + @object;
- IF OBJECT_ID(@object, N''U'') IS NOT NULL
- EXECUTE (@sql)',
- N'@db sysname, @find nvarchar(257)',
- @db = @db, @find = @find;
- END;
- CLOSE dbs; DEALLOCATE dbs;
- SELECT
- R.[Db_Name],
- [Table_Name] = @find,
- R.[Database Version]
- FROM @results AS R
- ORDER BY
- R.[Db_Name];
- EXEC dbo.sp_foreachDB @command1 = 'select [Database Version] from dbo.AWBuildVersion',
- @name_pattern = 'AdventureWorks%'
Add Comment
Please, Sign In to add comment