August 30, 2013 at 1:46 pm
In one database(Order), all my objects (in this db, some objects are not my app) for a app have to move to another server.
SQL server administrator want me to list all objects(tables, views, functions and store procedures).
Is there a easy to select objects and generate a script for administrator?
August 30, 2013 at 1:53 pm
adonetok (8/30/2013)
In one database(Order), all my objects (in this db, some objects are not my app) for a app have to move to another server.SQL server administrator want me to list all objects(tables, views, functions and store procedures).
Is there a easy to select objects and generate a script for administrator?
Right click the database in Object Explorer. Then Tasks -> Generate Scripts.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 30, 2013 at 1:55 pm
Thank you. It works.
August 30, 2013 at 1:57 pm
You're welcome.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 30, 2013 at 2:03 pm
If I only need object's name, how to do it?
August 30, 2013 at 4:21 pm
SELECT quotename(s.name) + '.' + quotename(o.name), o.type
FROM sys.objects o
JOIN sys.schemas s ON o.object_id = s.object_id
WHERE o.type NOT IN ('S', 'IT')
This will lists all objects in the database (save system objects). Now, how to discern your objects, I don't know. If they have something common in the name you can use that. Else you will need to do it by hand.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 1, 2013 at 7:04 pm
Sean Lange (8/30/2013)
adonetok (8/30/2013)
In one database(Order), all my objects (in this db, some objects are not my app) for a app have to move to another server.SQL server administrator want me to list all objects(tables, views, functions and store procedures).
Is there a easy to select objects and generate a script for administrator?
Right click the database in Object Explorer. Then Tasks -> Generate Scripts.
Just a note that when I did this in SQL 2008 R2 a few months back, it did not include triggers.
Possible I did something wrong.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 1, 2013 at 11:20 pm
Erland Sommarskog (8/30/2013)
SELECT quotename(s.name) + '.' + quotename(o.name), o.typeFROM sys.objects o
JOIN sys.schemas s ON o.object_id = s.object_id
WHERE o.type NOT IN ('S', 'IT')
This will lists all objects in the database (save system objects). Now, how to discern your objects, I don't know. If they have something common in the name you can use that. Else you will need to do it by hand.
Will this query works?
bcoz we dont have any object_id column in sys.schemas
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 2, 2013 at 1:03 am
kapil_kk (9/1/2013)
Will this query works? bcoz we dont have any object_id column in sys.schemas
Oops! That should be schema_id, not object_id.
SELECT quotename(s.name) + '.' + quotename(o.name), o.type
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type NOT IN ('S', 'IT')
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 2, 2013 at 1:06 am
dwain.c (9/1/2013)
Just a note that when I did this in SQL 2008 R2 a few months back, it did not include triggers.
Possible I did something wrong.
Tools->Options->Scripting
Here you can control what to include. If you run the scripting wizard you also get this dialog so that you can choose for the occasion. It is correct that triggers and indexes are not included by default.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 2, 2013 at 4:39 am
Erland Sommarskog (9/2/2013)
dwain.c (9/1/2013)
Just a note that when I did this in SQL 2008 R2 a few months back, it did not include triggers.
Possible I did something wrong.
Tools->Options->Scripting
Here you can control what to include. If you run the scripting wizard you also get this dialog so that you can choose for the occasion. It is correct that triggers and indexes are not included by default.
I managed a way around the missing triggers but I did not notice that INDEXes were not included. I didn't see any dialog that would have allowed selection of the triggers.
Is there a way to create a script to only create the INDEXes?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 2, 2013 at 1:51 pm
dwain.c (9/2/2013)
I managed a way around the missing triggers but I did not notice that INDEXes were not included. I didn't see any dialog that would have allowed selection of the triggers.
I see now that in SSMS 2012 you don't get this dialog, but you need to set the scription options separately.
I very rarely use this feature myself, this is why I had not noticed this difference.
Is there a way to create a script to only create the INDEXes?
Not that I can think of. Maybe from SSDT, but I would not really expect that.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 2, 2013 at 7:35 pm
If they're moving the app to another server, I'd hope they'd be moving the data, as well. Why not skip all the work with scripts and just do a restore from a backup?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2013 at 8:04 pm
Jeff Moden (9/2/2013)
If they're moving the app to another server, I'd hope they'd be moving the data, as well. Why not skip all the work with scripts and just do a restore from a backup?
In my case, it was a commercial app configured specific to a new client's requirements. Needed to start with completely empty tables.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 3, 2013 at 1:58 am
Jeff Moden (9/2/2013)
If they're moving the app to another server, I'd hope they'd be moving the data, as well. Why not skip all the work with scripts and just do a restore from a backup?
In the original post, the situation was that only the tables belonging to a certain application should be moved. Apparently there are other tables in the database that should not be moved.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply