How to create a new text file per stored procedure

[hwood125]

In powershell through sqlServer:\sql\servername\default\databases\databasename\storedprocedures> I am able to create a text or .sql file with all of the stored procs in the directory into one text file. I am wondering how I can script each stored proc into it's own .sql or txt file.

Here is what I have so far:

foreach($Name in dir) {GCI | % {$_.script()} | out-file C:\?.txt}

I have tried 'c:\'+$Name+'.txt', ('c:\'+$Name+'.txt'), 'c:\'+$_.Name+'.txt' , etc. I'm pretty new to powershell and I'm not sure what to do and I can't find out anything on-line that writes to a file without a specific name.

Any help would be appreciated.

Thanks

 

[EBGreen]

What about "C:\$name.txt"

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[hwood125]

When I write that it gives me an error mentioning:

+ for each ($name in dir){gci | % {$_.script()} | out-file <<<< "c:\$name.txt"}
Out-File : Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

I have no idea where to go from there?

 

[EBGreen]

I think you need to move your Out-File inside the preceding loop.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[hwood125]

I'm still a newbie here. When you say preceding loop and you talking about putting it inside the {} directly following the $_.script() and would I still have to pipe it to the out-file?

Is this what you mean:

foreach ($Name in Dir) { gci | % {$_.script() | out-file "c:\$Name.txt"}

?

Thanks!

 

[EBGreen]

If that works then yes, that is exactly what I meant. If not, then clearly I meant something else.


To be honest with you I'm not sure what you are trying to do. Dir would create a collection of objects representing all the files and folders in the current working directory. You are looping through that collection and for each item you are getting a collection of the files and folders in that item (gci). For each of those, you are trying to use the .script() method of each file or folder and you want to output the result of that method to a file with the same name as the parent folder.

Is that correct? I will point out that I am not aware of any .script() method for file or folder objects.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[hwood125]

You are exactly right up to the point about naming the files. I want to name each file with the name of the stored procedure that it is scripting out.

So if there are 10 procs in the dir then there will be 10 txt files each with the name of the proc that is in the directory.

Does that make better sense?

Thanks

 

[EBGreen]

I do not have SQL Server installed so it is hard for me to test, but do the procs have a .Name property? Are they stored as regular files? Do they have a .Script() method?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[hwood125]

There is a name property and there is a script method.

As far as I know they are all objects.

Not that I am an expert by any means but what I have so far will create a single text file with a static name. Inside the file it has the script of all of the stored procedures in the directory. So I'm thinking that there has to be a way to do this.

thanks

 

[EBGreen]

Let's try to be a little more explicit and see if that helps. See what happens with this:

foreach ($folder in Get-ChildItem)
{
foreach($proc in Get-ChildItem)
{
$procName = $proc.name
$fileName = "C:\$procname.txt"
$proc.script() | Out-File $fileName
}
}



[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[hwood125]

That did the trick!

The only thing that I changed was commenting out the first foreach ($folder in get-childitem) because it made an endless loop. So I removed that and it worked great.

Thank You for all of your hard work!!!

 

[markdmac]

Don't forget to tip the waiter....if EBGREEN's advice was helpful, a star would be in order.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.