0

I am using SQL Server 2012.

I have a db lets called it A in that database I have procedure backup which get all creation information of another database which is B.

Now the problem when I am trying to use SP_HELPTEXT inside my procedure backup it give the information of database A. Any suggestion to make within database B?

Scenario:

I need to backup all functions in database B. My procedure which do that job is in database A. I used dynamic SQL to reach the names of functions when I want to get the body of them by using SP_HELPTEXT I stoped here

2

I would recommend against sp_helptext in general.

SELECT [schema] = s.name, [object] = o.name, m.definition 
  FROM otherdatabase.sys.sql_modules AS m
  INNER JOIN otherdatabase.sys.objects AS o
  ON m.[object_id] = o.[object_id]
  INNER JOIN otherdatabase.sys.schemas AS s
  ON o.[schema_id] = s.[schema_id];

If you need the database name to be dynamic, you can do:

DECLARE @db sysname = N'otherdatabase';

DECLARE @exec nvarchar(520) = @db + '.sys.sp_executesql';

DECLARE @sql nvarchar(max) = N'SELECT [schema] = s.name, 
    [object] = o.name, m.definition 
  FROM sys.sql_modules AS m
  INNER JOIN sys.objects AS o
  ON m.[object_id] = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id];';

EXEC @exec @sql;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.