Run a query in Multiple Databases - SQL

we can use a inbuilt Stored procedure called 'sp_MSforeachdb' for querying multiple DBs.

this is a SP which run on all databases in currunt SQL Server insance.

ex:

declare @command varchar(100)
set @command='select ''?'''
exec sp_MSforeachdb @command

and this code will return all DB names as follows

you can get this result sets to a single table using table variable as follows


declare @t table (dbname varchar(100))
declare @command varchar(100)
set @command='select ''?'''

insert into @t
exec sp_MSforeachdb @command
select * from @t

ans also you can get details of your preferred databases only


declare @t table (DBname varchar(50),TotalObjects varchar(max))
DECLARE @command varchar(max)

SELECT @command = 'IF ''?'' IN(''ExcelineDev'',''ReportServer$SQL2012'',''TestDB1''
) BEGIN USE ?
EXEC
(''
select DB_NAME(DB_ID()), count(*) as TotalObjects from [?].dbo.sysobjects
'')

END'

insert into @t
EXEC sp_MSforeachdb @command
select * from @t

hope this would help

SQL Server View and view types

To understand the concept of a View, we'll directly go to an example

First I create two table called 'Employee' and 'Departments' and add data to those tables

Employee Table

Departments Table

• Assume that you need to store Employee with Department. In this point you can create a view. View is like a virtual table. A View doesn't store data. it is basically a table structure.
• We can restrict viewing unwanted data to the users using views

we can make a view as follows

create view viewEmployee_Dept
as

select emp.EmployeeName, dep.DepartmentName
from Employee as emp inner join Departments as dep on emp.DepartmentId = dep.deptid

viewEmployee_Dept













There are few types of Views, The operations which can do with views depend on these View types

1. System defined views - will be available in all user defined database

• INFORMATION_SCHEMA_TABLES
• sys.all_columns

     2.Information Schema views - display information of database, table, columns

• INFORMATION_SCHEMA.COLUMNS

     3.Catelog view - can get db special information

• sys.columns

     4. User defined views

• simple view - get data from a single table only

            ex: create view viewEmployee

                  as

                  select EmployeeName,Salary

                  from Employee

• complex view - getting data from multiple tables

           ex: create view viewEmployee_Dept

                 as

                 select emp.EmployeeName, dep.DepartmentName

                 from Employee as emp

         inner join Departments as dep on emp.DepartmentId = dep.deptid