Retrieve data from all database with same table schema

Question

I have created ASP.NET C# Web Application (with Database as SQL Server 2008). My Application Connects to Different databases (Say Count is 10). they all are have same structures i.e. Same Table Name,Stored Procedure Name,SP Argument Count & Type,Table Schema all are same.

But data inside is different.

I have stored procedure inside every database dbo.usp_getData1 which accepts some parameters do some table scans and retrieve data with respect to only that database.

My Question is How Can i Retrieve Data from all my Database using only one of the stored Procedure (As Parameters/Table Structure is same) ?

I can achieve same either by SQL Server or C# Coding.

Views will be helpful for me ?

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 
[ WITH <view_attribute> [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::= 
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     }

score 1 · Accepted Answer · edited May 23 '17 at 12:21

1

You can select from multiple databases (on the same server) if you fully qualify the table name like Database.Schema.Table.

See e.g. http://www.sqlteam.com/article/selecting-data-from-different-databases

If you want to pass the database names as a parameter to your stored procedure, you'll probably have to resort to dynamic SQL, although this could potentially open a whole new can of worms.

See e.g. SQL 2000 Table Name as variable in stored procedure and http://www.sommarskog.se/dynamic_sql.html

Simple example:

CREATE PROCEDURE SelectEverything
    @tableName NVARCHAR(250)
AS
BEGIN
    DECLARE @sql NVARCHAR(500)
    SET @sql = 'SELECT * FROM ' + @tableName;
    EXEC(@sql);
END
GO

If you execute this proc:

EXEC dbo.SelectEverything 'SomeOtherDatabase.dbo.SomeTable'

It will select everything of table SomeTable in schema dbo of database SomeOtherDatabase.

But please read the link about dynamic SQL.

edited May 23 '17 at 12:21

Community

1
1

answered Feb 12 '13 at 10:24

Andre Loker

8,368
1
23
36

So Can i Pass Database Name as Parameter to my SP And my SP Will return Data from Only That Database ? – Shaggy Feb 12 '13 at 10:38
Also How Can i Pass Multiple Database Name in my SP and my SP will combine the resultset into one ? – Shaggy Feb 12 '13 at 10:39
Yes. I added a simple example to my original answer. – Andre Loker Feb 12 '13 at 10:45
I have More than 10 Tables inside SP. Can i Pass Database Name Instead. And Write in SP Like `Use My SP Code` for All Database Name i Passed ? – Shaggy Feb 12 '13 at 10:51
To add multiple table or database names, see e.g. here http://www.sommarskog.se/arrays-in-sql.html or http://stackoverflow.com/questions/114504/is-it-possible-to-send-a-collection-of-ids-as-a-ado-net-sql-parameter To iterate over multiple values, follow the example in the links above or maybe use a [CURSOR](http://msdn.microsoft.com/en-us/library/ms180169.aspx). Use [UNION](http://msdn.microsoft.com/en-us/library/ms180026.aspx) to combine results from multiple selects. – Andre Loker Feb 12 '13 at 10:52

Frederiek · Answer 2 · 2013-02-12T11:08:29.583

0

You could use linq to sql and generate a dbml file.

And then you can acces every database with adjusting the connectionstring when initializing the datacontext:

MyDataContext firstDB = new MyDataContext(someConnectionstring);
MyDataContext secondDB = new MyDataContext(someOtherConnectionstring);

edited Feb 12 '13 at 11:08

answered Feb 12 '13 at 11:03

Frederiek

1,605
2
17
32

Retrieve data from all database with same table schema

2 Answers2