SQL query to script all objects from a database in order

Question

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

Answer 1

You can use SSMS to generate the script for you:

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

Where would you like to save it:

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