Generate Create Scripts

Hi there,

 I have made a stored procedure to create a script with a create statement for a table. it looks like this:

Create Procedure GenerateScript (
@tableName varchar(100))
as
If exists (Select * from Information_Schema.COLUMNS where Table_Name = @tableName
Begin
declare @sql varchar(8000)
declare @table varchar(100)
declare @cols table (datatype varchar(50))
insert into @cols values('bit')
insert into @cols values('binary')
insert into @cols values('bigint')
insert into @cols values('int')
insert into @cols values('float')
insert into @cols values('datetime')
insert into @cols values('text')
insert into @cols values('image')
insert into @cols values('uniqueidentifier')
insert into @cols values('smalldatetime')
insert into @cols values('tinyint')
insert into @cols values('smallint')
insert into @cols values('sql_variant')

set @sql=''
Select @sql=@sql
+case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type +
case when Data_Type in (Select datatype from @cols) then '' else '(' end
+case when data_type in ('real','money','decimal','numeric') then cast(isnull(numeric_precision,'') as varchar)+
','+case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar)
end when data_type in ('char','nvarchar','varchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar) else '' end
+case when Data_Type in (Select datatype from @cols)then '' else ')' end
+case when Is_Nullable='No' then ' Null,' else ' Not null,' end
from Information_Schema.COLUMNS where Table_Name = @tableName

select @table= 'Create table ' + table_Name from Information_Schema.COLUMNS where Table_Name = @tableName

select @sql=@table + substring(@sql,1,len(@sql)-1) +' )'
select @sql as DDL
End
Else
Select 'The table ' + @tableName+ ' does not exist'
GO

 

Now my problem. I want to install this SP in the master db and i want call other databases. With  "SP_RENAME" that's possible when i say "USE otherdatabase". With my own stored procedure it's not possible. What do i have to alter?

Thanks