Recently, I just wanted to get all the stored procedures, full text, on a file. This will give me power to do lot of things.
So, I came across of material. These are 3 approaches I followed.
Approach 1
Right Click on the database name -> Tasks -> Generate Scripts...
In the Choose Objects step,
Check the Stored Procedures.
Next step choose a destination file. Next. Finish.
Approach 2
This can be achieved by using a query as well, but be warned that there is a pitfall.
SQL Server truncates all the cell contents to 4000 characters. Tested on 2014.
SELECT
r.ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES r
You could use the columns ROUTINE_CATALOG, r.ROUTINE_SCHEMA, r.ROUTINE_NAME to get extra details.
Caution: SELECT query truncates cells to 4000 chars.
I have not found a direct and simple solution to this problem. (Please leave a comment if you have one).
Settings: You can access
Tools -> Query Results -> Results to Grid
to modify the settings. However, maximum length of Non XML data is 65535 only. Unable to go beyond that.
Approach 3
SELECT DEFINITION FROM SYS.SQL_MODULES
No comments:
Post a Comment