SQL Server Performance

Power Shell - How to script every stored proc to it's own file in a directory

Discussion in 'SQL Server 2008 General DBA Questions' started by hwood125, May 29, 2009.

  1. hwood125 New Member

    In powershell through sqlServer:sqlservernamedefaultdatabasesdatabasenamestoredprocedures> 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
  2. moh_hassan20 New Member

    Here , every stored procedure is created in a file with its name:
    Catch procedure Name in the variable $a
    foreach($Name in dir) {
    $a=$Name.Name
    $Name.Script() |out-file c:$a.txt
    }
  3. satya Moderator

    This is what referred in BOL too:
    foreach ($folder in Get-ChildItem)
    {
    foreach($proc in Get-ChildItem)
    {
    $procName = $proc.name
    $fileName = "C:$procname.txt"
    $proc.script() | Out-File $fileName
    }
    }

Share This Page