StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Create batch file with script (sorry about formatting, but it's really should be inline to execute batch):</p> <pre><code>osql -U %1 -P %2 -S %3 -d %4 -h-1 -Q "SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_TYPE = 'PROCEDURE'" -n -o "sp_list.txt" for /f %%a in (sp_list.txt) do osql -U %1 -P %2 -S %3 -d %4 -h-1 -Q "SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME = '%%a'" -n -o "%%a.sql" </code></pre> <p>Name it "run.bat". Now, to execute batch use params:<br> run.bat [username] [password] [servername] [database]<br> on example:<br> run.bat sa pwd111 localhost\SQLEXPRESS master<br> first all stored procedure names will be stored in file sp_list.txt, then one by one in separate script files. The only issue - last line of each script with result count - I'm workin' on it :) </p> <p><strong>edited</strong>: bug in query fixed</p> <p><strong>Removing "Rows affected" line</strong><br> Ok, now we need to create one more batch: </p> <pre><code>type %1 | findstr /V /i %2 > xxxtmpfile copy xxxtmpfile %1 /y /v del xxxtmpfile </code></pre> <p>Name it "line_del.bat". See, the first param is file to process, 2nd - string to search lines for removing. Now modify the main batch (again, sorry about formatting): </p> <pre><code>osql -U %1 -P %2 -S %3 -d %4 -h-1 -Q "SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_TYPE = 'PROCEDURE'" -n -o "sp_list.txt" call line_del sp_list.txt "rows affected" call line_del sp_list.txt "row affected" for /f %%a in (sp_list.txt) do osql -U %1 -P %2 -S %3 -d %4 -h-1 -Q "SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME = '%%a'" -n -o "%%a.sql" for /f %%a in (sp_list.txt) do call line_del %%a.sql "rows affected" for /f %%a in (sp_list.txt) do call line_del %%a.sql "row affected" </code></pre> <p>See related articles:<br> <a href="http://www.aumha.org/a/batches.php" rel="nofollow noreferrer">Simple programming commands in a batch environment</a><br> <a href="http://msdn.microsoft.com/en-us/library/aa214012(SQL.80).aspx" rel="nofollow noreferrer">osql Utility</a><br> <a href="https://stackoverflow.com/questions/513158/mssql-how-do-you-script-stored-procedure-creation-with-code">MSSQL: How do you script Stored Procedure creation with code?</a><br> <a href="https://stackoverflow.com/questions/418916/delete-certain-lines-in-a-txt-file-via-a-batch-file">Delete certain lines in a txt file via a batch file</a></p> <p>:) you may notice, last two are from SO!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload