You are here

tsql

sp_who2 Alternative (T-SQL, SQL Server)

The trusty "sp_who2" is a quick way to look at what's happening on your SQL instance, but completely lacks the ability to filter using a WHERE clause. You may have to manually sift through hundreds of results to find what you need.

Never fear.

Discover How User Is Accessing SQL Instance (PowerShell and T-SQL)

When your security gets unruly, it can get time consuming to figure out how a user is accessing a SQL instance. Sometimes you may know that a user is getting access via one certain AD group but you aren't sure if any other groups are granting access. Furthermore, a user might be in a group that's nested in another group, through which they are gaining access to your SQL instance.

TSQL - Make Excel Link In Output

Here is a situation I just came across (boiled down and simplified).  An end user was having to manually find a user id, navigate to an image folder, and then search through hundreds of images to find the one corresponding with that user.  Sure, they were in numerical order, but still, what a pain!  He wanted to generate an excel file with links to files to quickly access the corresponding image. 

TSQL - Get All Queries Currently Running

This is perhaps my most commonly used query when troubleshooting.

T-SQL - Get Listener Name on AlwaysOn Cluster

When using AlwaysOn, you must connect using your listener name, not your node name.  If you connect with the node name, it will only work until there is a failover, which would defeat the purpose of your High Availability setup, right?

T-SQL - Get Currently Running Jobs With Duration

Query to get all currently running jobs with their run duration in seconds.

Source: http://www.sanssql.com/2013/08/t-sql-query-to-find-currently-running.html

Restore All Databases With Most Recent Backup

This is a bit of a scary task.  Someone tells you about 87 databases that all need to be restored from the most recent FULL backups. You can either spend the rest of your day pointing and clicking, or you can use some PowerShell power to crank out the script and get on with your day. Here is the PowerShell way.

I don't like to fire-off the actual backups from PowerShell, rather I just use it to script out the restore script.  So, this PowerShell script will output the T-SQL restore script:

(See caution below before running this!)

SQL Server - Cluster Queries

Get some clustered facts about your server.

SQL Server - All Objects On Instance Sorted By Date

One common question I hear whenever something begins to go wrong is, "Has anything been changed recently?" Finding recent changes can be a good place to search, depending on the issue, so here is a script to do just that.

Validate SQL Code Without Running It

Validate SQL Code Without Running It

In a nutshell:

SET NOEXEC ON;
-- The script now will throw all DML errors without executing the code being run.

Pages

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
4 + 2 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.

Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer