question

ruancra avatar image
ruancra asked

Count and display words from a string

Hi all I need to query the definition column in sys.sql_modules. I am looking for all procs that reference the column called "balance", The problem is that there are also columns called "CashBalance" and "BonusBalance". The result set should look like this: Procname: ColumnName: ColumnCount: Proc1 Balance 1 Proc1 BonusBalance 3 Proc1 CashBalance 2 Proc2 Balance 2 Proc2 BonusBalance 1 Proc2 CashBalance 0 Thanks
tsql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered
The version of SQL Server is not mentioned, so lets assume that it is 2016 or newer, so the [string_split][1] is available. If it is not available then [Jeff Moden's CSV split function][2] is the best choice as an alternative which works in previous versions of SQL Server. The following considerations are used in the script below: - The columns in question may be last word on the procedure line in which case the carriage return/line feed combinations should be replaced with single space first so that the definition column can be split based on the space used as a delimiter - The columns may appear in the procedure text as is, or they may in the form of TableOrAliasName.ColumnName in which case the TableOrAliasName part needs to be stripped out - The column may be wrapped into single quotes, double quotes, or brackets, so all these characters need to be stripped out as well - The column may or may not be immediately followed by comma, so commas need to be stripped out as well - The parameter names need not be included (this explains the like '%[^@]Balance%' part - Once the cleansing is complete, some values might end up with spaces to the left and/or right of them, so the values need to be trimmed - Due to excessive amount of "cleansing", the query is split into 2 common table expressions in order to somewhat reduce the complexity associated with them Here is the script which will work: ; with records as ( select o.[name] ProcName, ltrim(rtrim( replace(replace(replace(replace(replace(sp.[value], '"', ''), '''', ''), '[', ''), ']', ''), ',', ''))) v from sys.sql_modules m inner join sys.objects o on m.[object_id] = o.[object_id] cross apply string_split(replace(replace(m.definition, char(13), ''), char(10), ' '), ' ') sp where o.[type] = 'P' and sp.[value] like '%[^@]Balance%' ), ready as ( select ProcName, case when charindex('.', records.v) = 0 then v else substring(records.v, charindex('.', records.v) + 1, 50) end ColumnName from records ) select ProcName, ColumnName, count(1) ColumnCount from ready where ColumnName in ('Balance', 'BonusBalance', 'CashBalance') group by ProcName, ColumnName order by ProcName, ColumnName; Again, if the SQL Server version is earlier than 2016, please replace the reference to **string\_split** with Jeff Moden's function. If the definitions are longer that 8K characters then some other alternative should be used, such as a split via XML. Hope this helps, Oleg [1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql [2]: http://www.sqlservercentral.com/articles/Tally+Table/72993/
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image Jeff Moden commented ·
Be advised that the XML split function may be much more trouble than it's worth. You have to de-entitize if you look for certain characters and it's actually quite slow. Even if turns out to be 3 times slower, the DelimitedSplit8K function could be modified to handle NVARCHAR(MAX) and still be faster than the XML splitter.
1 Like 1 ·
Oleg avatar image Oleg commented ·
@Jeff Moden Yep, this is precisely the reason I recommended and hyperlinked your CSV splitter, and it looks like OP opted to use it. There are no upvotes for this answer at this time but at least it was accepted. You might recall we had a discussion about it few years back, which caused me to eventually delete the answer I posted, even though it had 6 upvotes, which in turn allowed me to earn a rare "disciplined" badge awarded to someone who deleted own answer with 3+ upvotes :)
1 Like 1 ·
ruancra avatar image ruancra commented ·
Thanks for this detailed answer and useful function. The version is SQL 2014, so will have to use the CSV split function.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.