Browse by Tags

• splitting strings

• Looping over routines using sp_foreachroutine

  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. Read More... Posted Wednesday, July 12, 2006 10:45 PM by Adam Machanic | 0 Comments Filed under: T-SQL, Scripts

• Tokenize UDF

  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 Read More... Posted Wednesday, July 12, 2006 10:34 PM by Adam Machanic | 0 Comments Filed under: T-SQL, Scripts

• "Reflect" a TSQL routine

  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 Read More... Posted Wednesday, July 12, 2006 10:33 PM by Adam Machanic | 0 Comments Filed under: T-SQL, Scripts

• Script out PKs/UNIQUE constraints and referencing FKs

  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, Read More... Posted Wednesday, July 12, 2006 10:32 PM by Adam Machanic | 4 Comments Filed under: T-SQL, Scripts

• Pattern-based split string

  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 Read More... Posted Wednesday, July 12, 2006 10:31 PM by Adam Machanic | 4 Comments Filed under: T-SQL, Scripts

• Validate a URL from SQL Server

  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 Read More... Posted Wednesday, July 12, 2006 10:23 PM by Adam Machanic | 6 Comments Filed under: T-SQL, Scripts

• Counting occurrences of a substring within a string

  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 Read More... Posted Wednesday, July 12, 2006 10:22 PM by Adam Machanic | 2 Comments Filed under: T-SQL, Scripts

• Splitting a string of unlimited length

  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 Read More... Posted Wednesday, July 12, 2006 10:20 PM by Adam Machanic | 6 Comments Filed under: T-SQL, Scripts, splitting strings

• Pattern-based replacement UDF

  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', Read More... Posted Wednesday, July 12, 2006 10:07 PM by Adam Machanic | 0 Comments Filed under: T-SQL, Scripts