How to find a table when you don't know which database it belongs to?

By -
In this very short tip I'd like to share my solution to a yesterday quesiton in MSDN forum - how to find the database the table belongs to. I use my favorite idea of dynamically constructing a query using INFORMATION_SCHEMA.TABLES view.

DECLARE @table sysname, @SQL NVARCHAR(MAX)

SET @table = 'Items'

SET @SQL = ''

SELECT @SQL = @SQL + ';

IF EXISTS (SELECT 1 from ' + QUOTENAME(name) + '.INFORMATION_SCHEMA.Tables WHERE Table_Name = @table

AND TABLE_TYPE = ''BASE TABLE'')

PRINT ''Table ' + @table + ' found in ' + name + ''''

FROM sys.databases

 
EXECUTE sp_executeSQL @SQL, N'@table sysname', @table
That's all. Try it with the table which you may have in multiple databases. I used a bit undocumented way of concatenating multiple rows in a string, so I can not actually use ORDER BY in this code. To be strict, I need to use XML PATH('') approach here instead.

Comments

Post a Comment

Popular posts from this blog

Polymorphism concept

By -
Method Overloading ------------------ Method Overloading means having two or more methods with the same name but different signatures in the same scope. These two methods may exist in the same class or one in base class and another in the derived class. The signature of a function is determined by 3 factors: a) Number of arguments received by the function. b) Data types of the parameters/arguments. c) Position/order of the arguments. The signature of a function never depends upon the return type. Two functions differ only in their return type cannot be overloaded. Example of Method Overloading ----------------------------- public partial class _Default : System.Web.UI.Page { protected void Button1_Click(object sender, EventArgs e) { MyBaseClass mbc = new MyBaseClass(); Response.Write(mbc.AddNumbers(1,2).ToString()); Response.Write("<br />" + mbc.AddNumbers(1, 2, 3).ToString()); } } public class MyBaseClass { public int AddNumb...
Read more

What is ACID properties and How SQL Server Comply to the same

By -
What is ACID properties and How SQL Server Comply to the same Answer ACID  Propertied is Acronym for A- Atomicity-  Either all the operations/transactions are performed or None.Each transaction is said to be atomic if when one part of the transaction fails, the entire transaction fails and database state is left unchanged. C- Consistency-  This states that at a given moment of time the database is always in consistent. This guarantees that a transaction never leaves your database in a half-finished state. I- Isolation-  Isolation ensures that the transactions are separated(Isolated) from each other until they are finished. This means the transactions does not interfere with one another. D- Durability-  This ensures that once a transaction is commited it is available forever at any given moment of time. This guarantee that once the user has been notified of a transaction's success the transaction will not be lost, the transaction's data changes will survive ...
Read more

bulk insert into sqlserver using textfile

By -
Image
bulk insert into sqlserver using textfile Bulk insert sql server 2008 example I find so many issues while inserting bulk record in database.. Need to create temp table than I has to map all fields which is in file and in table than Insert it in to actual table but here is one Master command to insert record in bulk through simple statement.. Step 1 ) Create Table. create table AddressBook (id int,name varchar(50),address varchar(50)) Step 2) Create Text file with “,” delimiter. 1,John,India 2,Raj,India 3,Sonal,India 4,Roshan,India Step 3) Bulk insert record in to AddressBook table bulk insert AddressBook from 'c:\Addressbook.txt' with ( fieldterminator = ',' , rowterminator = '\n' )
Read more