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

• J.D.: 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).

Answer 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

• Viz Krishna: Thanks Peter. What I need is a query . When I give the database as a parameter . I need to get all those scripts.
• Peter: 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: 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.
• Viz Krishna: 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 .
• J.D.: @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.