How to Avoid temp table values after executing Stored Procedure

• manikandan-1115962

  Old Hand

  Points: 350

  More actions

  June 26, 2009 at 11:05 pm

  #135751

  Hi,

  I am writing one stored procedure to Delete All Column names in all database in single instance. but my procedure not holding datas in Temp table...

  Give me some suggestion for this... my Stored procedure is...........

  CREATE PROCEDURE USE_SP_DBSIMILARCOLUMNS_DML

  (

  @pcrColumnName VARCHAR(50)

  )

  AS

  BEGIN

  CREATE TABLE #temp1 (id int identity , name varchar(128))

  CREATE TABLE #temp2 ( table_name varchar(128), db_name varchar(128), Column_Name varchar(128) )

  CREATE TABLE #temp3 ( Column_name varchar(128) )

  INSERT INTO #temp1

  SELECT name FROM sys.databases where name not in('tempdb')

  DECLARE @var int

  DECLARE @db_name varchar(128)

  DECLARE @cmd varchar(1280)

  DECLARE @tsql VARCHAR(128)

  DECLARE @table VARCHAR(128)

  SET @var = 1

  WHILE @var <= (SELECT count (1) from #temp1)
  BEGIN
  SELECT @db_name = name from #temp1 where id = @var
  SET @cmd = 'SELECT a.name , db_name = '''+@db_name+''', b.name FROM '+ @db_name+'..sysobjects a inner join '+ @db_name+'..syscolumns b on a.id=b.id where a.xtype = ''U'''
  INSERT into #temp2 exec (@cmd)
  SET @cmd='SELECT Column_Name from #temp2'
  INSERT into #temp3 exec (@cmd)
  SET @var = @var+1
  END

  DELETE FROM #temp3 WHERE Column_name=@pcrColumnName

  SELECT column_name AS 'Columns' from #temp3 group by column_name having count(*) > 1

  DROP TABLE #temp1

  DROP TABLE #temp2

  DROP TABLE #temp3

  END

• arun.sas

  SSChampion

  Points: 11831

  More actions

  June 27, 2009 at 2:10 am

  #1016368

  manikandan (6/26/2009)

  ---

  DECLARE @var int

  DECLARE @db_name varchar(128)

  DECLARE @cmd varchar(1280)

  DECLARE @tsql VARCHAR(128)

  DECLARE @table VARCHAR(128)

  Hi,

  use the Nvarchar date type in Declare statements and then try,

  like..

  DECLARE @db_name Nvarchar(128)

  DECLARE @cmd Nvarchar(1280)

  DECLARE @tsql NVARCHAR(128)

  DECLARE @table NVARCHAR(128)

  And then use the exec sp_executesql @cmd instead of the exec (@cmd)

  ARUN SAS