Script multiple queries
-
Is it possible to script multiple queries? I need to transfer about 30 queries from one server to another w/o copying the entire database. I was hoping i wouldn't have to script out each query individually in SSMS.
-
by queries, i am guessing you mean tables, instead.
you can sue the metadata to script out the actual queries by getting the column names, is that what you are after? something like this?
SELECT DISTINCT
t.name,
'SELECT ' + sq.Columns + ' FROM ' + schema_name(t.schema_id) + '.' + t.name
FROM sys.tables t
JOIN (
SELECT OBJECT_ID,
Columns = STUFF((SELECT ',' + name
FROM sys.columns sc
WHERE sc.object_id = s.object_id
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
FROM sys.columns s
) sq ON t.object_id = sq.object_id
WHERE t.name IN(
'Analytics_Locations',
'Analytics_Services',
'Analytics_Campaign',
'Analytics_CampaignAsset'
)Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! - Jackie Lowery wrote:
Is it possible to script multiple queries? I need to transfer about 30 queries from one server to another w/o copying the entire database. I was hoping i wouldn't have to script out each query individually in SSMS.
There is no 'Query' object in a database. What do you mean?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
The only way i know to transfer a query right now is to select each one individually in SSMS and use the Script View As functionality. Is there a way to select more than one query at a time and get a script to create them all at once?
-
Right-click on the database in SSMS Object Explorer.
Select Tasks/Generate Scripts
Select Views and follow the prompts.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Crap. I googled it, but didn't search for the right terms apparently. Thanks guys.
-
To add to Phil,
You can also highlight and object in the object explore and then hit F7 to open Object Explorer Details.
Once in the OED you can navigate to folders highlight a group of objects in a folder and right-click to script as. The same is also useful if you wanted to script out all your jobs or be selective with what you want scripted out.
- inkosi_dba_dev wrote:
To add to Phil,
You can also highlight and object in the object explore and then hit F7 to open Object Explorer Details.
Once in the OED you can navigate to folders highlight a group of objects in a folder and right-click to script as. The same is also useful if you wanted to script out all your jobs or be selective with what you want scripted out.
This also works very well. I can ctrl+click everything and script at one time . Thanks !
-
The better option would be to have your database object definitions in your code store, so you can grab them and run them in the new server.
- GaryV wrote:
The better option would be to have your database object definitions in your code store, so you can grab them and run them in the new server.
+1 million to that!!! That would also mean that you have them in some sort of a source control program, which is an imperative so far as I'm concerned.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Like a visual studio code store?
- Jackie Lowery wrote:
Like a visual studio code store?
Like Source control in TFS or GitHub
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply