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