Use sqlcmd utility to generate stored procedures lists

Question

I know we can do this by going Tasks > Generate Scripts > Stored Procedure.

Is there a way to do it using sqlcmd? I'm planning to write a batch file to automate it.

You can do this with SSDT. Which can also validate, version and deploy your schema and changes — Panagiotis Kanavos, Dec 15 '16 at 14:24
Possible duplicate of [How can I automate the "generate scripts" task in SQL Server Management Studio 2008?](http://stackoverflow.com/questions/483568/how-can-i-automate-the-generate-scripts-task-in-sql-server-management-studio-2) — TheGameiswar, Dec 15 '16 at 14:42
This may help also. http://stackoverflow.com/questions/7567530/how-to-script-a-stored-procedure-from-tsql — SS_DBA, Dec 15 '16 at 14:49

score 1 · Answer 1 · answered Dec 15 '16 at 14:49

You could combine the system table sys.procedures and the function object_definition.

-- Return the name and definition of first three SPs in the current db.
SELECT TOP 3
    Name,
    OBJECT_DEFINITION(object_id) AS SQLStatement
FROM
    sys.procedures
ORDER BY
    Name
;

Word of warning; If a procedure is renamed using sp_rename, the old definition is returned. This is a closed bug on connect.

Use sqlcmd utility to generate stored procedures lists

1 Answers1