SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON 
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[p_add_row_id]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_add_row_id]
GO

CREATE procedure dbo.p_add_row_id
	@SOURCE_TBLNM VARCHAR(100)
as

begin

  declare 
	@final_cmd varchar(8000),
	@cmd_1 VARCHAR(4000)

  SET NOCOUNT ON

-- check if table already has a column row_id
create table #tmp_1 (COLUMN_NAME varchar(100) )

set @cmd_1 = ' SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
		WHERE TABLE_NAME = ''' + @SOURCE_TBLNM + ''' AND COLUMN_NAME = ''row_id'' '

INSERT INTO #tmp_1(COLUMN_NAME)
exec (@cmd_1)

-- column row_id exist
IF (  ( select count(*) from #tmp_1 ) > 0 )
		return 0

-- add column row_id to the table with identity column and 
-- add column row_id to the table without identity column


declare @sql_end varchar(50), @newline varchar(10), @cmd varchar(8000), @db_cmd varchar(1000)

set @sql_end = '
go'

set @newline = '
'
set @db_cmd = ''

--set @final_cmd = @final_cmd + ' + @sql_end  + @newline

-- identity and row_id is not exists
SELECT @cmd = 'ALTER TABLE ' + so.name + ' ADD row_id as ' + sc.name 
FROM dbo.sysobjects so 
	inner join dbo.syscolumns sc
		on sc.id = so.id
WHERE so.type = 'U' and sc.autoval is not null and
	so.name =  @SOURCE_TBLNM    
and not exists ( SELECT * 
		FROM INFORMATION_SCHEMA.COLUMNS 
		WHERE TABLE_NAME = @SOURCE_TBLNM   AND COLUMN_NAME = 'row_id')

IF (  LEFT(IsNull(@cmd,'Z'),5)  <> 'ALTER' )
	set @cmd = 'ALTER TABLE ' + @SOURCE_TBLNM + ' ADD row_id int identity(1,1) '

set @cmd = @db_cmd + @cmd + @sql_end  + @newline

print (@cmd)

end

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO