Originally posted here.  Of all of the undocumented stored procedures shipped with SQL Server, there are two in particular that I constantly use: sp_MSforeachtable and sp_MSforeachdb. These procedures internally loop over each non-Microsoft shipped (i.e. user-defined) table in the current database, or each database on the current server, ...

Originally posted here.  Yes, another string splitting UDF from a guy who's obvioiusly become obsessed with TSQL string splitting. This time we delve into a mysterious world that I call, ''Tokenization.'' So what is Tokenization? It's a word I made up for this problem. But what is it, really? It's splitting up a string based on ...

Originally posted here.   Ever want to see the text of a stored procedure, function, or trigger -- or manipulate the text in some way? sp_helptext works, sort of. But I really don't like the way it handles large procedures (> 4000 characters). They seem to end up with some strangely wrapped lines and other side-effects that aren't ...

Originally posted here.  In the course of my work, I occasionally need to cluster a primary key that's nonclustered, or go the other way, or make some other modification to a primary key... But it's a hassle! All of the foreign keys need to be dropped, the PK needs to be dropped, and then everything needs to be re-created. Scripting all ...

Originally posted here.  ''hickymanz'' asked in the SQL Server Central forums for a method of counting unique words in a text column. Wayne Lawton recommended using a string split function, which was a good idea, but not quite adequate for the job in my opinion. Typical string split functions, like this one that I wrote can handle only ...

Originally posted here.   File this one in your folder of things you should probably never use -- but maybe, some day, in an emergency, you'll need this. I see posts requesting this functionality all the time. ''How do I validate a URL in SQL Server?'' Not just the string, but the URL itself -- how can we find out if it's ...

Originally posted here.  I have absolutely no idea why anyone wants to do this, but I keep answering the same question in forums: ''How do I count the occurrences of a substring [note: usually comma] within a string?''   In an effort to thwart carpal tunnel syndrome, I have created the Ultimate Substring Occurrence Counting ...

Originally posted here. There are many techniques for splitting a string in T-SQL (in other words, taking a character-delimited string and producing a table of the values), the best of which are encapsulated in Erland Sommarskog's famous article. My favorite of his string splitting techniques is adapted from a previous example that was created ...

Originally posted here. As a personal challenge, I decided to write a UDF that will work just like T-SQL's REPLACE() function, but using patterns as input. The first question: How does REPLACE() handle overlapping patterns?  SELECT REPLACE('babab', 'bab', 'c')-------------------------------------------------- cab(1 row(s) ...