Easily produce and use a table of numbers
A numbers table is a versatile tool that can be used to synthesize ranges of various kinds of data. For instance, you can write queries against a numbers table to identify gaps or breaks in…
Generate an HTML table from an SQL query
Sometimes it’s useful to be able to generate an HTML table based on the data produced by a query. I’ve worked with clients who have needed this done as part of an internal automated notification…
Return query results in the exact same order every time
Query result ordering is not guaranteed. If your query does not include an ORDER BY clause, the order of your results is completely arbitrary. This is because tables are sets, which themselves have no order.…
Output constants in the rows of a SELECT statement
What if you need to create a temporary table, but you don’t want or need to create a table variable or temporary table? One option is to ‘synthesize’ a table using constants — also known…
Get the version of SQL Server & Windows Server with a SELECT
If all you care about is the version alone, then all you need is… In my work, I find I also sometimes need to know more information about the underlying system. Which produces:
Pass a table as a parameter to a stored procedure
1. Create a new user defined table type (UDTT). A table type is conceptually like a temporary table, but you can pass this dude into a stored procedure as a parameter. This is handy if…
See the most recently updated stored procs, tables & views
This group of similar queries return the object name, creation date and last modified date of various objects in the database.
Search names of tables or stored procedures for arbitrary text
Sometimes you need to search for a stored procedures without knowing its exact name, or to find all of the procedures that begin with a certain naming prefix. Tables that follow a particular naming convention…
Find text in any table or any stored procedure
Typically I want to find text somewhere in a table or a stored procedure. There are plugins that exist to help you search — but in a pinch you can search the database manually too.…