You may want to find for example the date of the 4th Saturday in each month for a given year. This function came out of answering the question here: http://stackoverflow.com/questions/33694768/how-to-get-list-of-2nd-and-4th-saturday-dates-in-sql-server.
I’ve created it as a Table Valued Function so you can bind it into any query you wish.
Tony created a Table-Valued Function, which is handy but leads me to the classic User-Defined Function reminder: they tend to cause performance problems. One alternative is a dedicated date table with attributes like day of week and nth day of month.
Here’s one of my favorites, which searches for code within stored procedures, functions, and views:
SELECT OBJECT_SCHEMA_NAME(sm.object_id) AS SchemaName, OBJECT_NAME(sm.object_id) AS ObjectName, CONCAT(OBJECT_SCHEMA_NAME(sm.object_id), '.', OBJECT_NAME(sm.object_id)) AS FullName, CONCAT(OBJECT_SCHEMA_NAME(sm.object_id), '.', OBJECT_NAME(sm.object_id), ',') AS CommaSeparatedName, definition FROM sys.sql_modules sm WHERE sm.definition LIKE '%DEFINITION%' --AND OBJECT_SCHEMA_NAME(sm.object_id) = 'Something' --AND OBJECT_NAME(sm.object_id) = 'Something' ORDER BY SchemaName, ObjectName;
Shawn Melton shows us how to make SQLPS load a bit faster, and which comes with the obligatory warning:
WARNING: You are modifying the files at your own risk. You have been warned.
If you are not familiar with the files involved with a module, you can read more on that here. The file I found most interesting is the “SqlPsPostScript.PS1” file, located in the SQLPS module folder for the given version of SQL Server:
Check it out. Those two seconds you save add up over time.
This is effective, but I struggle a little with the SQL query. It’s good, but suffers from the structure of the jobs tables in MSDB. We have to account for that and it makes the SQL query a little convoluted. It would be helpful if we could reference a simple data set like the Job Activity Monitor in SSMS.
Of course, this is a leading question on my part. There is a way to do this and it is by leveraging the SQL Server Management Objects (SMO). This .Net library is the API interface for working with SQL Server and is what SSMS is built on. Because it is a .Net library, we can also access it through Powershell.
SMO’s a powerful thing.
Andy Galbraith has a tale of woe and a cautionary message:
Paul’s blog post “Issues around DBCC CHECKDB and the use of hidden database snapshots” discusses the need to have certain permissions to be able to create the snapshot CHECKDB uses. I checked the DATA directory and the SQL Server default path and found that the service account did have Full Control to those locations.
What happened next ultimately resolved my issue, and it reflects something I constantly tell people when they ask me how I research things relatively quickly (most of the time anyway :)) – whenever you read a blog post or article about a subject, MAKE SURE TO READ THE FOLLOW-UP COMMENTS! Sometimes they are nothing beyond “Great Article!” but quite often there are questions and answers between readers and the author that add important extra information to the topic, or just “Don’t Forget This!” style comments that add more detail.