Tag Archives: cursors

Find the database when table name is known

Requirement:

We have a table name or at least a part of it with us.  We want to know the database to which it belongs. Manually searching is not possible/ difficult as there are many databases having many more tables.

Solution:

We will create a stored procedure which accepts table name as a parameter. The procedure searches for the table name parameter across all user-defined databases in the server. If there is/are any match/matches the program will print out the server name. If there is no match, program prints the same.

Source code:

SET ANSI_NULLS ON
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Satyanarayana Kondapalli
-- Description: find the database in which the required table is present
-- =============================================
CREATE PROCEDURE getDBNameByTable @tblname VARCHAR(80)=NULL
AS
BEGIN
SET NOCOUNT ON;
--check if a parameter is passed as table name
IF (@tblname IS NULL)
BEGIN
PRINT 'Please pass a table name as parameter'
GOTO Error
END
IF OBJECT_ID('tempdb.dbo.#dbnames', 'U') IS NOT NULL
DROP TABLE #dbnames
IF OBJECT_ID('tempdb.dbo.#searchtable', 'U') IS NOT NULL
DROP TABLE #searchtable
CREATE TABLE #searchtable (table_name VARCHAR(80))
DECLARE @name VARCHAR(80)
DECLARE @searchquery VARCHAR(max)
DECLARE @count int
-- create a temp table with all database list
SELECT name INTO #dbnames from sys.databases where name not in ('model','msdb','master','tempdb')
DECLARE findmydb CURSOR FOR
SELECT name from #dbnames
OPEN findmydb
FETCH NEXT FROM findmydb INTO @name
SET @count=0
WHILE @@FETCH_STATUS = 0
BEGIN
--iterate every db to get tables list from each db
SELECT @searchquery='SELECT TABLE_NAME FROM '+@name+'.'+'information_schema.TABLES'
INSERT INTO #searchtable exec (@searchquery)
--if the table is found, print the db name
IF exists(SELECT table_name from #searchtable where table_name like '%'+@tblname+ '%')
BEGIN
SET @count=@count+1
PRINT @name
END
--truncate the table after each iteration
TRUNCATE TABLE #searchtable
FETCH NEXT FROM findmydb INTO @name
END
IF(@count=0)
--if table is not found at all
PRINT 'table does not exist in the server!'
CLOSE findmydb
DEALLOCATE findmydb
Error:return -1
END
GO

Sample:
EXEC getDBNameByTable 'TABLE_NAME'

Generate insert scripts from a result set of SQLserver table

Requirement:
We have a table called employee which contains two columns eno, esal. The table contains ten records.
We are supposed to create a similar table with the same values in other server/database. We can create the same table by using the same CREATE table definition. Using the below method we can generate insert scripts which we can run in the destination, to add data rows. This method uses the concept of cursors.
Method:
Employee table has the following definition:

CREATE TABLE [dbo].[employee](
[eno] [int] NULL,
[esal] [int] NULL
)

Both the employee number, and salary are integers.
Below steps need to be executed in the source server/Database:

  1. Click on Results to File icon in the SQL Editor menu in Management Studio. This can be used to generate all the insert scripts in a text file. Shortcut to do this is (Ctrl+ Shift+ F).
  2. Run the below cursor query:


DECLARE @eno INT,@esal INT
DECLARE insertquery CURSOR FOR
SELECT eno,esal FROM empsalary
OPEN insertquery
FETCH NEXT FROM insertquery INTO @eno,@esal
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'insert INTO empsalary values('+CONVERT(VARCHAR,@eno)+','+CONVERT(VARCHAR,@esal)+')'
FETCH NEXT FROM insertquery INTO @eno,@esal
END
CLOSE insertquery
DEALLOCATE insertquery

  1. Running the above query will open file explorer. Select all files and save the file as insert query.txt.
  2. Use that file to add records in destination server.