How to search inside Stored Procedures?

A common problem faced by many developers when it comes to databases and SQL Server is how to search the text inside a stored procedure.

In many systems particularly older Classic ASP solutions, functional code has been moved from the actual application to stored procedures inside the database. This is usually because it will either run faster here, or because it was just a lot easier to perform the necessary task using TSQL. Following this though comes the problem of how you can search what’s in all those stored procedures, especially when you’re getting into the hundreds of them. Let’s say there was a Users table that contained fields for an address, but that now needs to be moved to a table of its own, you would need to search all the code for things accessing those table columns but SQL Server Management Studio certainly doesn’t provide any search box’s with the power to do this.

Never fear though syscomments is here. Syscomments contains the original text from amongst other things all the Stored Procedures in the DB s all you need to do is search that for what you’re looking for:

Select OBJECT_NAME(id), [text]
From syscomments
Where [text] like ‘%Create%’

The function OBJECT_NAME will also help you by converting the id number in the result set into the actual name of the stored procedure (or view, function etc). If you wanted to limit the result to just stored procedures you can add the following line to the where clause:

AND OBJECTPROPERTY(id, ‘IsProcedure’) = 1

Advertisements

Refreshing an UpdatePanel using JavaScript

Update panels provide a really easy way to add partial page refresh’s to a web page in order to give a more user friendly experience that’s more like a common desktop App. By default a button within that update panel will cause a post back and so long as no elements outside of the update panel are affected in the post back only it will refresh. However what do you do if you need to refresh a panel based on something outside of the update panel. One option is just to place a hidden button within the Update Panel and trigger that to post back, however this appears a little hacky. The solution is to use JavaScript and the _doPostBack method.

The following bit of code is all you really need:

<script type=”text/javascript”>
function refreshUpdatePannel(param) {

var prm = Sys.WebForms.PageRequestManager.getInstance();
prm._doPostBack(‘<%=UpdatePanel1.ClientID %>’, param);
prm.add_endRequest(refreshUpdatePanelComplete);
}

function refreshUpdatePanelComplete() {
//add code here for when the update pannel is refreshed
}
</script>

All you need to add is something to call the function.

The second parameter is optional and provides a way of passing some information back to the server to let it know what has just happened on the page.

When the update panel has finished refreshing the refreshUpdatePanelComplete function will fire so more JavaScript can be added if needed e.g. To reset whatever just happened on the page that caused the initial postback.