SQL Generate Script to update tables on another server

We have a development database on one server and a live production database on another server.  We need to generate scripts from the development server (we just did an extensive update, primary keys and foreign keys) for most of the tables.  I have not been able to find how to "Generate a script of just the keys to run on the production server" with Microsoft SQL Server Management Studio      10.50.6000.34 and SQL 2008 R2, .  I can take the production server offline during the update, I just can't loose their data.

Regards
Sam
SamCashAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
That option is not available in any SQL Server tool. You'll need a 3rd party software to do that.
You can generate Production database script and check it against the Development database script and then write a script with the ALTER commands needed to have the Production database with the same structure as Development.
0
Deepak ChauhanSQL Server DBACommented:
In order to avoid the manull efforts.

1.You can compare schema using Redgate software. using Trial or full version.

2. You can compare it using SSDT (Micorsoft SQL server data tool), This tool is available with
Visual Studio Ultimate and Visual Studio Premium.

Note:: If you have sql server 2012 installed at least free version (SQL 2012 Express) you can get this tool installed inplace of Bussiness intellegence development studio.

Through SSDT tool you can do schema comparision.
0
x-menIT super heroCommented:
you can script anything with powershell.

import-module <the SQL 2008 module>

script the object:

$(gi \SQL\<hostname>\<instacename>\databases\<MyDB>\tables\<MySchema>.<MyTable>\Indexes\<MyPK>).script()
$(gi \SQL\<hostname>\<instacename>\databases\<MyDB>\tables\<MySchema>.<MyTable>\ForeignKeys\<MyFK>).script()
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

SamCashAuthor Commented:
Please wait I am studying and trying PowerShell, I took the weekend off.  
Regards
Sam
0
x-menIT super heroCommented:
The sqlps utility starts a PowerShell session with the SQL Server PowerShell provider and cmdlets loaded and registered. You can enter PowerShell commands or scripts that use the SQL Server PowerShell components to work with instances of SQL Server and their objects.

more about sqlps: https://technet.microsoft.com/en-us/library/cc280450(v=sql.105).aspx
0
SamCashAuthor Commented:
Thanks for the assistance.  I found full solution:



http://www.mssqltips.com/sqlservertip/3443/script-all-primary-keys-unique-constraints-and-foreign-keys-in-a-sql-server-database-using-tsql/

Open in new window


Regards
Sam
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
So I misunderstood your question. I thought that you wanted only to create the non-existence ones but looks like by the scripts that they are dropping everything and create new ones, meaning that what exists in DEV and do not exist in PROD will be lost as well.
For that you could use the "Generate Scripts" option from SQL Server Management Studio.
0
SamCashAuthor Commented:
Victor, (and I hope this helps others).

I need to make PRODuction exactly like DEVelopment.

1) Run the 2 scripts to generate scripts to "create all keys' on DEV
2) Run the 2 scripts to generate scripts to 'drop all keys' on PROD
3) Run the 2 generated scripts (from step 2) dropping all keys on PROD
4) Run the 2 generated scripts (from step 1) creating all keys on PROD

SSMS 2008 R2, I could not find options in "Generate Scripts" to deal with just keys, except dropping and creating the table which losses the data.  Am I missing something, is there a better option?  Sorry about the lack of clarity of my original question.

Regards
Sam
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you click on the Advanced button? You can set what you want or not to be scripted there:
GenerateScriptsAdv.PNG
0
SamCashAuthor Commented:
Victor,

Yes I use "Advanced".  The issue I have is "I do not know which advanced option prevents "DROP and CREATE" table, which losses all the data on the PRODuction server.  The data on the DEVelopment server is not the same.

If there is an option to select 'script ONLY PK, FK, IX, CONSTRAINT, UK' and NOT the table along with if "NOT EXISTS" and "DROP and CREATE" will in effect copy all the keys from DEV to PROD without destroying all the data on the PROD server. (except this may leave some old preexisting keys on PROD which I can manually delete, or run the DROP scripts on PROD first).

Of course, if there is an option to preserve all the data on the target (PROD) would be even better.

I am new to SQL.  Again, Thank You for sharing your experience.
Sam
0
SamCashAuthor Commented:
Victor,

I ran the 4 step procedure today, ugh, all went well, except
1. I had to manually remove all the GO statements from the Create Scripts
2. I had to change the FILLFACTOR = 50, from 0, must be between 1 and 100 %

3. It did not script my INSERT and UPDATE Specification to: Delete Rule = Cascade ( I think ssms is miss-labeld should be DELETE and UPDATE Specification)

The scripts to generate scripts are way over my head, but it appears all I need is #3, how to include the INSERT and UPDATE Specification in the Create Keys scripts

Thanks Much
Sam
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
1. I had to manually remove all the GO statements from the Create Scripts
Why? You should not have issues with the GOs.

2. I had to change the FILLFACTOR = 50, from 0, must be between 1 and 100 %
If it was 50 is because was defined like that. You need to change it in DEV or else next time you'll need to do the same.

3. It did not script my INSERT and UPDATE Specification to: Delete Rule = Cascade ( I think ssms is miss-labeld should be DELETE and UPDATE Specification)
Sorry, I don't understand what you want to say. Can you explain with an example?
0
SamCashAuthor Commented:
Victor,

1. The generated scripts from SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL FOREIGN KEY CONSTRAINTS and SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL PK AND UNIQUE CONSTRAINTS look like...

ALTER TABLE [dbo].[ContactsTypes] WITH CHECK ADD CONSTRAINT [FK_ContactsTypes_Contacts] FOREIGN KEY([ContactID]) 
REFERENCES [dbo].[Contacts] ([ContactID]) 
GO
ALTER TABLE [dbo].[ContactsTypes] WITH CHECK ADD CONSTRAINT [FK_ContactsTypes_ContactsTypesList] FOREIGN KEY([ContactTypeID]) 
REFERENCES [dbo].[ContactsTypesList] ([ContactTypeID]) 
GO
ALTER TABLE [dbo].[ContactsPhoneNumbers] WITH CHECK ADD CONSTRAINT [FK_ContactsPhoneNumbers_Contacts] FOREIGN KEY([ContactID]) 
REFERENCES [dbo].[Contacts] ([ContactID]) 
GO
ALTER TABLE [dbo].[ContactsPhoneNumbers] WITH CHECK ADD CONSTRAINT [FK_ContactsPhoneNumbers_ContactsPhoneTypesList] FOREIGN KEY([ContactPhoneTypeID]) 
REFERENCES [dbo].[ContactsPhoneTypesList] ([ContactPhoneTypeID]) 
GO

Open in new window


I kept getting a "syntax error near GO", after a lot of googling and trial and error, I found some posts that said the cr/lf may be the problem, that UNIX, and some other os I do not remember, and I think some other db's use different line termination characters, They said, to fix this either use Notepad++ and fix them or just delete them, none of this made sense but I tried it anyway, by mistake I first deleted the "GO" replace all "", and everything compiled and executed, I am sure they meant delete the cr/lf or what ever the termination characters were.  But because it worked I went on to the next issue.

2. Below the script generates a ", FILLFACTOR =0".  Which I see is set at the DEV "db server options" to "0", which is Microsoft's default.  However, according to Microsoft the sql statement "FILLFACTOR = n" n must be between 1 and 100, go  figure.  After googling fillfactor for a suggested default value, I was not able to find any consensuses.  So I arbitrarily picked the middle 50%.  If you have a suggestion, Thank You.  I think I can delete the generation of scripting the FILLFACTOR, It may be optional and I can leave it alone (at 0) for now?

ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED(ContactID ASC) 
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF, FILLFACTOR =0) ON [PRIMARY];
 
ALTER TABLE [dbo].[ContactsCardingsList] ADD CONSTRAINT [PK_ContactsCardingsList] PRIMARY KEY CLUSTERED(ContactCardingID ASC) 
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF, FILLFACTOR =0) ON [PRIMARY];
 
ALTER TABLE [dbo].[ContactsCredentials] ADD CONSTRAINT [PK_ContactsCredentials] PRIMARY KEY CLUSTERED(ContactID ASC) 
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF, FILLFACTOR =0) ON [PRIMARY];
 
ALTER TABLE [dbo].[ContactsDegreeNamesList] ADD CONSTRAINT [PK_ContactsDegreeNamesList] PRIMARY KEY CLUSTERED(ContactDegreeNameID ASC) 
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF, FILLFACTOR =0) ON [PRIMARY];
 
ALTER TABLE [dbo].[ContactsGendersList] ADD CONSTRAINT [PK_ContactsGendersList] PRIMARY KEY CLUSTERED(ContactGenderID ASC) 
WITH (PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, SORT_IN_TEMPDB = OFF, FILLFACTOR =0) ON [PRIMARY];
 

Open in new window


Victor, I can send any of these links if you like, just ask.  I will address the #3 in the next post.

Regards
Sam
0
SamCashAuthor Commented:
Victor,

More on # 2;

https://msdn.microsoft.com/en-us/library/ms177459.aspx

"Note: Fill-factor values 0 and 100 are the same in all respects."

I think I can set the default vale to 100% on my DEV db then when it is scripted it should be 100% instead of 0.

This should keep me from having to edit the output in the future.

Regards
Sam
0
SamCashAuthor Commented:
Victor,

Continuing #3...

Here is the generated script output from DEV for update to PROD of the FK by SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL FOREIGN KEY CONSTRAINTS:
https://www.mssqltips.com/sqlservertip/3443/script-all-primary-keys-unique-constraints-and-foreign-keys-in-a-sql-server-database-using-tsql/

ALTER TABLE [dbo].[Games] WITH CHECK ADD CONSTRAINT [FK_Games_FacilityDiamonds] FOREIGN KEY([DiamondID]) 
REFERENCES [dbo].[FacilityDiamonds] ([DiamondID]) 

Open in new window


Here is the generated script output from DEV for update to PROD of the table by SSMS:
USE [HW]
GO

/****** Object:  Table [dbo].[FacilityDiamonds]    Script Date: 07/02/2015 13:59:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FacilityDiamonds](
	[DiamondID] [int] IDENTITY(1,1) NOT NULL,
	[DiamondName] [nvarchar](50) NOT NULL,
	[ContactID] [int] NOT NULL,
 CONSTRAINT [PK_FacilityDiamonds] PRIMARY KEY CLUSTERED 
(
	[DiamondID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[FacilityDiamonds]  WITH CHECK ADD  CONSTRAINT [FK_FacilityDiamonds_Contacts] FOREIGN KEY([ContactID])
REFERENCES [dbo].[Contacts] ([ContactID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[FacilityDiamonds] CHECK CONSTRAINT [FK_FacilityDiamonds_Contacts]
GO

Open in new window


As you can see the first script does not have the;
ON UPDATE CASCADE
ON DELETE CASCADE

Adding this to the -SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL FOREIGN KEY CONSTRAINTS- is beyond my skill set at this time.  Is this type of assistance included in this service?

Kind Regards
Sam
0
SamCashAuthor Commented:
Victor,

I see a possibility to use SSMS 2008 R2;

1.  DEV, Tasks->Generate Scripts, CREATE (everything) Schema Only
2.  PROD, Take Off Line
3.  PROD, Tasks->Export Data (Save live data for later)
4.  PROD, Execute Script to CREATE (everything) Schema Only
5.  PROD, Tasks->Import Data (From step 3)
6.  PROD, Bring On Line (Now PROD should be exactly like DEV including Keys, Indexes and CASCADE... and customers PROD data preserved)
7.  PROD, NEVER LET PROD GET OUT OF SYNC AGAIN.  (I added features to the product without maintaining all the keys etc.  Now, due to shear volume it would be very difficult to perfectly remove extra keys and add all the new keys manually to make PROD exactly like DEV.)

I hope these 7 steps will better explain what I need to do.  It is difficult and dangerous to just test these things on the public PROD server.  If I loose data I may loose Job :-)...  Dose this or something like this with SSMS work?  I prefer to use SSMS rather than something I found on the internet...  How do others update their live servers?  (note: above Step 2.5 back up PROD)

Many Thanks, thanks, thanks...
Sam

ps last post
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I kept getting a "syntax error near GO",
It could be something else. It did say in which line you got the error?

After googling fillfactor for a suggested default value, I was not able to find any consensuses.  So I arbitrarily picked the middle 50%.  If you have a suggestion,
Fill factor is the space to be filled in an index page (8KB) and the default is 0 (100% if you like) that means to not leave any free space. I would stick on the Microsoft's default of 0 unless you have performance issues. Setting it to 50% means to use half page only (4KB) and left the other half empty, so your indexes will need double of space that they need now.

Adding this to the -SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL FOREIGN KEY CONSTRAINTS- is beyond my skill set at this time.  Is this type of assistance included in this service?
Experts that are answering here doesn't work for EE. We are here because we like to help. There's no limit but our knowledge, time and good will :)
Anyway why are you insisting in that script? SSMS gives you a more complete option.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
By the way, do you have any tool that can compare two text files?
My idea is:
1.  DEV, Tasks->Generate Scripts, CREATE (everything) Schema Only
2.  PROD, Tasks->Generate Scripts, CREATE (everything) Schema Only
3.  Use the compare tool to see where are the differences
4.  Copy and save the differences to a new script file
5.  Backup PROD
6.  Restore the PROD backup in DEV to a new database
7.  Run the script saved in 4 on the database created on 6
8.  If tests are OK, Backup Prod and run the script saved in 4 on the PROD database
0
SamCashAuthor Commented:
Vitor, (sorry for misspelling your name previously)

I kept getting a "syntax error near GO",
It could be something else. It did say in which line you got the error?
Every line with the "GO"

After googling fillfactor for a suggested default value, I was not able to find any consensuses.  So I arbitrarily picked the middle 50%.  If you have a suggestion,
I am setting default to 100%, this should work with the script if that proves to be required,

Anyway why are you insisting in that script? SSMS gives you a more complete option.
I DO PREFER TO USE SSMS!  
I have not been able to see or find out  how to use or configure SSMS to yield the result I need.

Does the 7 step SSMS procedure I sent yesterday look correct?  
By the way, do you have any tool that can compare two text files?
Yes I can do a text diff.  (Since using VS I have not for a long time, I used to use windiff,)  

I will try your 8 step SSMS procedure.  

What strikes me initially is, SSMS "Tasks->Generate Scripts" only scripts ["CREATE" or "DROP and CREATE"] the [TABLE plus the options picked under Advanced] which looses all the data.  (Not loosing data is why I thought maybe Export Data and Import Data may be what I am supposed to be doing. )  Nowhere have I been able to see an option to ALTER, I have only found [CREATE or DROP and CREATE ] options.  That just gave me an idea, maybe I can edit the script from your step 4, change "CREATE" to "ALTER" ?

Is there an option for "ALTER" in SSMS?
Is there an option for scripting Keys and Indexes (only) in SSMS?
Is "Export Data, do he update, then Import Data" with SSMS the generally accepted method?

Thanks Again
Sam
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Does the 7 step SSMS procedure I sent yesterday look correct?  
Looks ok if you want to recreate everything, including data.

I will try your 8 step SSMS procedure
The one I gave will only create the objects that doesn't exists in PROD. Doesn't DROP nothing.

Is there an option for "ALTER" in SSMS?
No. The idea of that option in SSMS is to create a script that will (re)create the objects.

Is there an option for scripting Keys and Indexes (only) in SSMS?
Yes, in the Advanced Options.

Is "Export Data, do he update, then Import Data" with SSMS the generally accepted method?
No. Only Inserts. And can also recreate the table (DROP & CREATE).
0
SamCashAuthor Commented:
Vitor,

I tried the 8 step procedure you provided - Good idea but because the two servers script the objects in different orders, the DIFF file was not manageable.

Is there an option for scripting Keys and Indexes (only) in SSMS?
Yes, in the Advanced Options.

I have not been able to make the Advanced options work,  -Table- must be chosen to get the Keys and Indexes options enabled, the script DROPS the Table to recreate it with Keys and Indexes, so you loose all your Data!  What am I missing??  

Continuing to try to get SSMS scripting to provide a solution..
I tried
- Script the entire DEV db, CREATE Schema Only
- Copy the PROD db to HW_BU (preserve the data)
- DROP the PROD db
- Run Script to CREATE everything
- IMPORT DATA from HW_BU...  everything worked until here, because SSMS imports the data in alphabetical order I get errors due to Key Violations when a TABLE is getting populated that is keyed to another table that has not been populated yet, SSMS does not take into account relationships and do the tables in dependency order.  They should import all the data THEN check for violations...  I could not find a switch or option to control this?

I just need to create a script from DEV to CREATE all the KEYs and INDEXes on PROD?

Thanks for all your help.

Regards
Sam
0
x-menIT super heroCommented:
$(gi \SQL\<hostname>\<instacename>\databases\<MyDB>\tables\<MySchema>.<MyTable>\Indexes\<MyPK>).script()
 $(gi \SQL\<hostname>\<instacename>\databases\<MyDB>\tables\<MySchema>.<MyTable>\ForeignKeys\<MyFK>).script()
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
(..)the script DROPS the Table to recreate it with Keys and Indexes, so you loose all your Data!  What am I missing??  
What about removing the DROP TABLE lines before running the script?

because SSMS imports the data in alphabetical order I get errors due to Key Violations
Yes, that's a crap. You'll need to run it again to get it working (first run creates the records in master table and second time will create the data in the detail tables). If you have a 3rd detailed level then you'll need to run it once more :(
0
x-menIT super heroCommented:
Powershell for All:

#Import SQL Powershell module
PS > import-module sqlps -DisableNameChecking

#Navigate to your database
PS SQLSERVER:\>cd SQL\<Hostname>\default\databases\AdventureWorks2012\tables>

#Get Tables as string, not as object
PS SQLSERVER:\SQL\<Hostname>\default\databases\AdventureWorks2012\tables>$tables= foreach ($table in $(ls)) { $table.ToString().replace("[","").replace("]","")}

#script all indexes to file C:\temp\idx.sql
PS SQLSERVER:\SQL\<Hostname>\default\databases\AdventureWorks2012\tables>$($tables | % {ls $_\indexes}).script() >> "C:\temp\idx.sql"

#script all FKs to file C:\temp\FK.sql
PS SQLSERVER:\SQL\<Hostname>\default\databases\AdventureWorks2012\tables>$($tables | % {ls $_\ForeignKeys}).script() >> "C:\temp\FK.sql"
0
SamCashAuthor Commented:
x-men,

This is foreign to me but I am eager to learn...    

I believe I have PowerShell, I right click on Object Explorer and get context menu with PowerShell, which opens a DOS like command window.  I have not been able to get just the "PS" prompt.  

Here are my results;

PS SQLSERVER:\SQL\LENOVO-TONY\DEFAULT> cd..
PS SQLSERVER:\SQL\LENOVO-TONY> cd..
PS SQLSERVER:\SQL> cd..
PS SQLSERVER:\> cd..
PS SQLSERVER:\> PS > import-module sqlps -DisableNameChecking
> : Cannot open file because the current provider (SqlServer) cannot open a file.
At line:1 char:5
+ PS > <<<<  import-module sqlps -DisableNameChecking
    + CategoryInfo          : InvalidArgument: (:) [], PSInvalidOperationException
    + FullyQualifiedErrorId : ReadWriteFileNotFileSystemProvider
PS SQLSERVER:\> 

Open in new window

PS SQLSERVER:\SQL\LENOVO-TONY\DEFAULT\Databases\HW> cd tables
PS SQLSERVER:\SQL\LENOVO-TONY\DEFAULT\Databases\HW\tables> $tables= foreach ($table in $(ls)) { $table.ToString().replac
e("[","").replace("]","")}
PS SQLSERVER:\SQL\LENOVO-TONY\DEFAULT\Databases\HW\tables> $($tables | % {ls $_\indexes}).script() >> "C:\temp\idx.sql"
script : Method invocation failed because [System.Object[]] doesn't contain a method named 'script'.
At line:1 char:38
+ $($tables | % {ls $_\indexes}).script <<<< () >> "C:\temp\idx.sql"
    + CategoryInfo          : InvalidOperation: (script:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

PS SQLSERVER:\SQL\LENOVO-TONY\DEFAULT\Databases\HW\tables> $($tables | % {ls $_\ForeignKeys}).script() >> "C:\temp\FK.sq
l"
script : Method invocation failed because [System.Object[]] doesn't contain a method named 'script'.
At line:1 char:42
+ $($tables | % {ls $_\ForeignKeys}).script <<<< () >> "C:\temp\FK.sql"
    + CategoryInfo          : InvalidOperation: (script:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

PS SQLSERVER:\SQL\LENOVO-TONY\DEFAULT\Databases\HW\tables>

Open in new window


Thank you for your assistance.

Kind Regards
Sam
0
x-menIT super heroCommented:
From:
PS SQLSERVER:\SQL\LENOVO-TONY\DEFAULT>
-navigate to :
PS SQLSERVER:\SQL\LENOVO-TONY\DEFAULT> cd Databases\HW\tables
-list schema and tables:
PS SQLSERVER:\SQL\LENOVO-TONY\DEFAULT\Databases\HW\tables>ls
-navigate to :
PS SQLSERVER:\SQL\LENOVO-TONY\DEFAULT\Databases\HW\tables>cd <TableSchema>.<MyTable>
PS SQLSERVER:\SQL\LENOVO-TONY\DEFAULT\Databases\HW\tables\<TableSchema>.<MyTable>
<MyTable>>cd indexes

-list indexes:
PS SQLSERVER:\SQL\LENOVO-TONY\DEFAULT\Databases\HW\tables\<TableSchema>.<MyTable>
<MyTable>\indexes>ls

-pick one and run:
PS SQLSERVER:\SQL\LENOVO-TONY\DEFAULT\Databases\HW\tables\<TableSchema>.<MyTable>
<MyTable>\indexes> get-item <MyIndex> | get-member

see if there is a method called script

PS SQLSERVER:\SQL\LENOVO-TONY\DEFAULT\Databases\HW\tables\<TableSchema>.<MyTable>
<MyTable>\indexes> $( get-item <MyIndex>).Script()
0
SamCashAuthor Commented:
X-men,

Yeah!  That all works.  I am beginning to see how PowerShell works.

It did output to command window a script to ALTER TABLE... ADD CONSTRAINT... good!

I went back to your last post (with more knowledge) things seem to be working, execept
PS SQLSERVER:\SQL\LENOVO-TONY\DEFAULT\Databases\HW\Tables> $tables=foreach ($table in $(ls)){$table.ToString().replace("[","").replace("]","")}
PS SQLSERVER:\SQL\LENOVO-TONY\DEFAULT\Databases\HW\Tables> $($tables | % {ls $_\indexes}).script()
script : Method invocation failed because [System.Object[]] doesn't contain a method named 'script'.
At line:1 char:38
+ $($tables | % {ls $_\indexes}).script <<<< ()
    + CategoryInfo          : InvalidOperation: (script:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

Open in new window


I do see Script() Method under \Indexes, and it output a Script from your post today.

I am guessing I am in the wrong directory, or the two lines need to be combined or...  I am just trying stuff... to learn more about PowerShell and maybe discover my solution while I am waiting for your next post.

I have goggled SQL PowerShell [commands, etc] are you aware of an organized listing of commands?

Thanks Much
Sam
0
SamCashAuthor Commented:
X-men,

Found cmdlets on MSDN, and how to use Get-Help and much more.  

Still trying to script  - foreeach table script indexes -, making progress,  eagerly awaiting your postback!

Thanks Again
Sam
0
x-menIT super heroCommented:
$($tables | % {ls $_\indexes}).script()

$tables holds the list of tables formatted like <schema>.<table>

% is alias to "foreach", so, foreach element of $tables, list it's "index" folder (<schema>.<table>\indexes)

enclosing it in $() treats it as the "calculated object" so that you can invoke the .script() method.


I have tested it on my system, and it is working fine.  

after running:
$tables= foreach ($table in $(ls)) { $table.ToString().replace("[","").replace("]","")}

if you type:
$tables
, it will output its contents. check if it lists your tables formatted like <schema>.<table>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SamCashAuthor Commented:
Thanks for the assistance.  Sorry I was on vacation.   Power Shell rocks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.