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

Is this a hard one or am i being unclear?

Thanks

|||

up (and i'm not saying this twice :p) (sorry)