Archive for the ‘SQL Server’ Category.
September 11, 2024, 2:21 pm
The collation on my production SQL Server database unfortunately does not show me case-sensitive values when I do a group by, which is kind of inconvenient when your code is doing string matching.
In order to see this kind of differences, you have to introduce COLLATE entry like this example, where Items is the table name and Category is the column name of the NVARCHAR field that I want to do the case-sensitive group on:
SELECT Category COLLATE SQL_Latin1_General_CP1_CS_AS, COUNT(Category)
FROM Items
GROUP BY Category COLLATE SQL_Latin1_General_CP1_CS_AS |
SELECT Category COLLATE SQL_Latin1_General_CP1_CS_AS, COUNT(Category)
FROM Items
GROUP BY Category COLLATE SQL_Latin1_General_CP1_CS_AS
November 16, 2023, 12:56 pm
Did you ever find yourself in a situation where you have a SQL Server database that has grown out of control, and you need to figure out where a call to insert a record into a particular table might be lurking in one of those triggers?
Oh sure, you can go into each table, expand the triggers folder in each table, then crack open each trigger showing and see if what you are looking for is there. For a database of non-trivial size, I would rather look up “tedium” in the dictionary.
Alas, there is a way to quickly screen down the number of places you may have to look for to find what you need. Open up your database and paste this text into the query window, of course replacing SearchText with whatever table name you need to search for:
SELECT so.name, text FROM sysobjects so, syscomments sc
WHERE TYPE = 'TR' AND so.id = sc.id AND text LIKE '%SearchText%' |
SELECT so.name, text FROM sysobjects so, syscomments sc
WHERE type = 'TR' AND so.id = sc.id AND text LIKE '%SearchText%'
For example, if you wanted to find any trigger that inserts a record into a table called Customers, you would do this query:
SELECT so.name, text FROM sysobjects so, syscomments sc
WHERE TYPE = 'TR' AND so.id = sc.id AND text LIKE '%insert into Customers%' |
SELECT so.name, text FROM sysobjects so, syscomments sc
WHERE type = 'TR' AND so.id = sc.id AND text LIKE '%insert into Customers%'
Keep in mind that unless you played with the settings or collation of your database, the case of the text in the LIKE statement above should not matter.