0

i have a script to get all objects (functions ,views ,triggers and procedures from a database . I need to create this in another database . The problem is some procedures may have another procedures or function calls inside it . So I need to create this in the right order. Any idea how to do that? ,and any idea to solve the memory issues that may cause while crating all these objects at the same time. I am adding my code here.

DECLARE @Test TABLE (Id INT IDENTITY(1,1), Code varchar(max))

INSERT INTO @Test (Code)
SELECT 
           'IF object_ID(N''[' + schema_name(schema_id) + '].[' + Name + ']'') IS NOT NULL 
           DROP '+
           case when type='fn' then 'Function'
                when type='TF' then 'Function'
                when type='IF' then 'Function'
                when type='FT' then 'Function'
                when type='FS' then 'Function'
                when type='TR' then 'TRIGGER'
                when type='V' then 'VIEW'
                
                       else 'Procedure' end+
           
           
           ' ['+ schema_name(schema_id) +' ].[' + Name + ']' + char(13) + char(10) + 'GO' + char(13) +char(10) + OBJECT_DEFINITION(OBJECT_ID) + char(13) +char(10) + 'GO' + char(13) + char(10)

            from sys.objects
            where is_ms_shipped = 0
            and type in ('FN','TF','TR','V','P','FS','FT','IF')


            --select * from @Test

DECLARE @lnCurrent int, @lnMax int
DECLARE @LongName varchar(max)

SELECT @lnMax = MAX(Id) FROM @Test
SET @lnCurrent = 1
WHILE @lnCurrent <= @lnMax
      BEGIN
            SELECT @LongName = Code FROM @Test WHERE Id = @lnCurrent
            WHILE @LongName <> ''
               BEGIN
                   print LEFT(@LongName,8000)
                   SET @LongName = SUBSTRING(@LongName, 8001, LEN(@LongName))
               END
            SET @lnCurrent = @lnCurrent + 1
      END

I am new in coding and not as skilled as you guys. So forgive if there are any mistakes. Thanks

1
  • You're missing a lot of objects this way, including tables, indexes, and constraints, etc. Like I mentioned in my comment in another post, this is not something you'll want to do with T-SQL code. Use a migration tool, such as even an SSDT database project, which will track the changes for you, or minimally you'll want to write application code to do this (not T-SQL code).
    – J.D.
    Nov 24, 2022 at 14:06

1 Answer 1

1

You can use SSMS to generate the script for you:

enter image description here

Select entire database, or if you only need specific items, click the latter.

enter image description here

Where would you like to save it: enter image description here

After next, next, finish, you have the create script in the right order:

enter image description here

5
  • Thanks Peter. What I need is a query . When I give the database as a parameter . I need to get all those scripts. Nov 24, 2022 at 8:13
  • I guess that will be a lot harder, where will you display the results? For very large databases I can imagine the maxlength will be too small to show your whole script.
    – Peter
    Nov 24, 2022 at 9:03
  • And.. Are there that many databases you need to do this for that you can't use the functionality that already exists? I think, in the time you will create your own custom script, you can have gone through the GUI lots of time. But that's up to you.
    – Peter
    Nov 24, 2022 at 9:05
  • Actually yes. I have a lot of databases that needs to do the same thing , so I need a scrip for that 🙂 thanks any way . Nov 24, 2022 at 9:22
  • 1
    @VizKrishna Assuming you're trying to keep multiple databases' schemas in sync, you should have one golden database as the template, that you use to apply new changes to first. Then you can use Peter's suggestion to script that database once (or use a migration tool to compare to) and apply those scripts to all of the other databases you want to synchronize the schemas on.
    – J.D.
    Nov 24, 2022 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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