Cade Bryant


Technical Article

Search all objects in all DBs for code fragments

This procedure allows you to search through all objects in all databases on your server for words/phrases in your object code.  Very handy for cases in which, for example, a column name on a table has been changed and you need to search your entire server for any sprocs/views/etc. that might reference it.Syntax: [EXEC] sp_FindCodeStr […]

You rated this post out of 5. Change rating

2003-06-10

259 reads

Technical Article

Improving performance on joins to large tables.

In your tenure as a DBA/developer, you've probably been asked to write dozens of stored procedures similar to this:CREATE PROCEDURE ContactInfo@ContactID intASSELECT c.Name, a.Address, p.PhoneFROM Contact cLEFT JOIN ContactAddress aON c.ContactID = a.ContactIDLEFT JOIN ContactPhone pON c.ContactID = p.ContactIDWHERE c.ContactID = @ContactIDBut there is a more efficient way to write such a query.....particularly if you […]

4 (1)

You rated this post out of 5. Change rating

2003-06-03

1,411 reads

Technical Article

Show all User-Defined Datatypes for all Databases

This procedure displays all user-defined datatypes in all databases on a server.  Another example of the power of dynamic T-SQL, this procedure dynamically generates SELECT statements for the systypes tables in each database and UNIONs them together so that they display in one recordset.

You rated this post out of 5. Change rating

2003-05-30

154 reads

Technical Article

Dynamically Generating HTML Tags from T-SQL

Here's a real-life challenge I was faced with at work: my company (a talent agency) needed me to create a report listing all of our actors that we represent - and for each actor, a comma-delimited string of each production that they've starred in.  Simple enough, right......except that, within the comma-delimited list of productions, they […]

You rated this post out of 5. Change rating

2003-04-11

765 reads

Technical Article

View Input Buffers for all SPIDS

This procedure, sp_AllInputBuffers, uses dynamic T-SQL to generate and execute the DBCC INPUTBUFFER statement for each server process (except yours).  Perfect for performance troubleshooting situations, when you want to see what commands are being executed against your server.  Also, a great example of the power of dynamic T-SQL.For the @exec parameter, pass 1 or leave […]

You rated this post out of 5. Change rating

2003-04-04

646 reads

Technical Article

"RESTORE..... WITH MOVE" all databases on a server

If, like me, you are constantly restoring/moving several databases between multiple environments (development to staging, production to training, etc.), you know how tedious it can get to use EM, or to manually type out all those RESTORE...WITH MOVE statements in QA.Even having a saved script isn't the ideal thing, as you still need to go […]

2 (1)

You rated this post out of 5. Change rating

2003-02-10

1,780 reads

Technical Article

Correction to "drop/recreate objects" script.

Regarding the recent script I submitted (dropping/recreating all procedures/views) - I made an important oversight. I neglected to add a CASE statement in order to make sure that the appropriate type of object was being referenced in the DROP statement.Below is the corrected script:

5 (1)

You rated this post out of 5. Change rating

2003-02-06

207 reads

Technical Article

Drop and re-create all stored procedures or views

There are times when you may need to drop and re-create all stored procedures and/or views in your database.  For example, in cases where procedures or views are causing blocked locks or other performance problems, a recent article (http://www.sswug.org/see.asp?s=1166&id=13448) suggested dropping/re-creating procedures and views after a service pack has been installed.  The installation of a […]

1.67 (3)

You rated this post out of 5. Change rating

2003-02-05

1,254 reads

Technical Article

The case against using single-line comments

This isn't a "script" per se - but rather an observation about a common issue in writing scripts.I want to alert SQL programmers to the issues surrounding the use of single-line comments.  I try to avoid these whenever possible, and use block comments instead (except, of course, when commenting out the keyword GO).The reason is […]

3.67 (3)

You rated this post out of 5. Change rating

2003-01-21

273 reads

Blogs

The Book of Redgate–Being Reasonable

By

As a part of the Book of Redgate, we have a series of (red,...

Azure Data Factory Pipeline Debugging Fails with BadRequest

By

I recently had a new pipeline fail. It was actually a copy of an...

Unlocking Real-Time Financial Insights: The Power of Microsoft Fabric

By

Microsoft Fabric is transforming real-time analytics for financial institutions. It provides a unified data...

Read the latest Blogs

Forums

How to get result from deleted row

By mashispano

Hi Guys I have a store proc that delete a row from a and...

Count of Distinct Values

By jagjitsingh

Hi If value is 21000030 it should return 4 not 5 . In first...

Joining against min date only

By darrell.woolaver 91826

This is one of those things very hard (for me)to explain in words. See...

Visit the forum

Question of the Day

Rolling Back CUs

Can I rollback a cumulative update and remove it from my SQL Server instance?

See possible answers