running microsoft sql server 11.0.3128 on windows server 2012 r2 essentials i attempting return name of specific database based on supplied variable (batch file calls sql script).
the process, in head, should this:
each database in instance in current database return databasename if variable found in column the code i've been working far looks this:
exec dbo.sp_msforeachdb ' use [?]; select db_name() dbname union select columnname dbo.items columnname =''variable'' ' problem is, returns lot more want since returns "null" values databases not contain "variable" , creates messages databases not containing "columnname".
but can't seem figure out how specific info want without other stuff. first time poster, please let me know if can improve question.
thanks!
edit: oops, didn't realize @ first working mssql , not mysql. principle below still work; you'll need adjust syntax bit , use user-function replace group_concat since mssql doesn't have that.
here's approach without sp_msforeachdb. note want sanitize parameters first.
delimiter $$ create procedure finddatabases ( in varname varchar(2000), in tablename varchar(2000), in columnname varchar(2000) ) begin declare selectquery varchar(2000); select group_concat( concat('select ''', table_schema, ''' databasename ', table_schema, '.', tablename, ' ', columnname, ' = ''', varname, '''') separator ' union ') databasenames information_schema.tables table_name = tablename @selectquery; prepare preparedsql @selectquery; execute preparedsql; deallocate prepare preparedsql; end $$ delimiter ; example usage:
call finddatabases ( 'variable', 'items', 'columnname' ) this procedure generates sql query each database table name matching table name supplied, unions them together, , executes them. each query in union returns database name if specified table in database has column matching specified name contains value matches specified variable name. databases matching these requirements present in query results, don't have worry null values in results.
additional edit: promised, here sqlserver version.
create procedure finddatabases ( @varname varchar(2000), @tablename varchar(2000), @columnname varchar(2000) ) begin declare @selectquery nvarchar(2000) -- first, list of database names contain specified table if object_id('tempdb.dbo.#db_temp') not null drop table #db_temp create table #db_temp (databasename sysname) select @selectquery = ( select ' use [' + d.name + ']; insert #db_temp (databasename) select db_name() databasename exists( select 1 sys.objects [object_id] = object_id(''' + @tablename + ''') , [type] = ''u'' )' sys.databases d d.name not in ('master', 'tempdb', 'model', 'msdb') , d.state_desc != 'offline' xml path(''), type).value('.', 'nvarchar(max)') exec sys.sp_executesql @selectquery -- use mysql's group_concat function turn list bunch of union select statements select @selectquery = ( select left(database_names , len(database_names ) - 10) database_names #db_temp extern cross apply ( select 'select ''' + databasename + ''' databasename ' + databasename + '.dbo.' + @tablename + ' ' + @columnname + ' = ''' + @varname + '''' + ' union ' #db_temp intern xml path('') ) pre_trimmed (database_names) group database_names ) drop table #db_temp -- run select statements exec sp_executesql @selectquery end to run it:
exec finddatabases 'somevar', 'items', 'columnname' i shamelessly pulled snippets here , here work around lack of group_concat function , sqlserver's information_schema having local database's info , not sharing information across databases.
Comments
Post a Comment