Entries Tagged as 'SQL'

Searching for Stored Procs in a Database related to a Table

SQL No Comments »

Maybe already widely used but I thought this was worth bloggin!

I was looking to update some functionality in an application which mean't searching through the code for any references to a particular table.

Although there were some references to the table in the pages I also had to ensure that any Stored Procs and Views etc. were also updated.

I used the following useful little piece of TSQL to find the stored procs that contained any reference to my table (obviously change the text "TABLE_NAME_GOES_HERE" below to suit):

SELECT DISTINCT so.name, so.type

FROM syscomments sc

INNER JOIN sysobjects so ON sc.id = so.id

WHERE sc.text LIKE '%TABLE_NAME_GOES_HERE%'



This returns a list like below:

========================================================

ssp_ApproveBatch P
ssp_DeleteProducts P
ssp_DeleteSales P
ssp_GetOrderedItems P
ssp_GetSalesSummary P
vw_SalesInputters V

P - Indicate a stored procedure
V - Indicates a view
========================================================

This is very basic script and can probably be built on i.e. create a SP with this code which accepts the table name as argument etc. but time is limited ATM.

Bookmark and Share

Using Stored Procedures Within CF Applications

Best Practices , ColdFusion , SQL No Comments »

I found a very good article entitled "Harnessing the Power of SQL Server Using Stored Procedures".

It details how and why stored procedures should be used rather than embedded SQL in cfquery's.

Details can be found here: http://coldfusion.sys-con.com/read/45571.htm

Bookmark and Share

SQL Server Temporary Tables Vs Table Variables

SQL No Comments »

Just to keep the blog from drying up, here's an article by Ben Forta which describes the options when using temp tables in your stored procs.

SQL Server Temporary Tables Versus Table Variables

Bookmark and Share

Resetting the identity of a table in SQL

SQL No Comments »

This is the SQL command to change the identity value


DBCC CHECKIDENT ('table name', RESEED, new value)

Bookmark and Share

Select a Random Record from the DB using SQL

SQL No Comments »

Nice wee snippet for selecting random rows from SQL:


SELECT TOP 1 ColumnName

FROM         TableName

ORDER BY     NewID()

Bookmark and Share
Powered by Mango Blog. Design and Icons by N.Design Studio
RSS Feeds