Downloads

download-to-storage-driveThis is the download page, where I’ve collected large scripts that aren’t practical to post in their entirety into a post, as well as other downloads. I’ve made an honest effort to test and verify them all, but bear in mind that I don’t have access to every type of advanced system setup and environment, so some of them may not work as planned on your particular server.

Read the complete disclaimer and copyright notice at the bottom of the page.


text-file sp_ctrl3: A replacement for sp_help

A little hack that does what sp_help does – displays information about a database object. This procedure, however shows a bit more information, and uses a more “copy-paste” friendly layout so you can script the object more easily. Add “sp_ctrl3” as a keyboard shortcut in Management Studio to quickly view the definition of any database object. Download

text-fileDecrypt SQL modules

This is the complete script as shown in the article Decrypting SQL objects. See the article for a full walk-through on what the script does. Download

text-file Display active sessions and locks

This script is useful to identify long-running or blocked queries, including which resources are locked or blocked/blocking and loads of other information about the state of your server. Download

text-file Calculate recurring dates

This is the fn_recurringDates() function featured in a post on creating a table value function that returns a series of dates based on a recurring pattern. Download

text-filesp_help_permissions – show effective permissions

A stored procedure that displays effective permissions as well as explains all inheritance paths that lead to them. Download (or download a previous attempt).

text-file Copying data from XML into tables

This is a script that I use to atomically insert relational data into multiple tables from a single XML blob. The XML blob can be built using the FOR XML AUTO construct. Download

text-file Synchronizing logins from another server

This stored procedure will fetch logins, user-defined server roles and memberships from another server (like an AG primary replica) and sync those to the local instance. Download

text-file Visualization of SQL Server Agent jobs

A quick hack to create a visual gantt-style bar chart of all SQL Server Agent job executions. Download

text-fileFind primary key candidates

A script that will automate the process of identifying candidates for a unique/primary key constraint on any given table. Download

 


General disclaimer and copyright notice

These are scripts that I’ve written and made available to you under the condition that you take responsibility for any consequences that may arise from using them. I do not recommend running them directly in production-like environments without thorough testing.

Important:

I cannot assume any responsibility regarding the accuracy of any output information, performance impacts on your server, whether the code performs correctly in your environment or any other consequences of using these downloads. It’s free, so it’s your ass. If your juristiction does not allow for this kind of waiver/disclaimer, or if you do not accept these terms, you are NOT allowed to store, distribute or use this code in any manner.

Copyright/CC:

These documents are made available under the Creative Commons 4.0 (with attribution) license. In short, this allows you to freely use, modify and redistribute them without charge, though you have to quote the source (the URL of this blog, https://sqlsunday.com/, is fine).

12 thoughts on “Downloads

  1. Howdy! None of the links for the downloads worked in my experience. I would love to take a look at them and compare to queries I currently have. Always trying to learn from the experts – Thanks!

  2. Probably the links are not allowed at John’s place of employment. Same thing happened to me when attempting to log in via my work computer.

  3. Hi Daniel,
    I’ve just tried your sp_ctrl3, very nice indeed!
    I have one suggestion, which is to add “OPTION (MAXRECURSION 0);” to the CTE for indexes.
    Without it I get the error “The maximum recursion 100 has been exhausted”, wich seems to be caused by our tables being heavily partitioned (often by day).
    Thanks,
    Michel

    • So glad you like it, and thanks for the feedback! Looks like you’ve got a pretty wide table with 100+ columns. :)
      I’ve updated the script – let me know of that works for you.

      • Wow you are quick ;-)
        Ah yes it was the columns… 114 to be exact.
        Thank you for the update!
        Cheers,
        Michel

  4. Hi Daniel
    I’m testing “Synchronizing logins from another server”
    My collation is _CS_ and the script do not filter correct.
    Fx. NT Service\% logins.
    After adding UPPER to both sides it’s working
    UPPER(sp.[name]) NOT LIKE UPPER(‘NT Service\%’)
    Thanks
    Jesper

  5. Suggestions for “Synchronizing logins from another server”:
    1. Filter out logins like ‘##%’ (or ‘##%##’) to remove processing of SQL internal certificate based logins.
    2. Not a must (but I’ve changed the proc to do this for us): Dynamically create the Linked Server with a unique name with pass thru authority (I based name on timestamp) with the primary replica as the target….then when done with it, drop it (all in the proc). I don’t like linked servers in general and certainly don’t like them cluttering up instances if I can help it. Of course. the user executing the proc will need permission to manipuliate linkled servers…but in our context, they will in every case.
    3. I would not put this in your script but I did for us: If Allow Drop is specified, the Print Only is turned on automatically. We have non-AG related logins in certain circumstances so we want to inspect all drops beforehand. So two runs…one to sync, and one to inspect and manually run drops. Not a heavy price to pay for our piece of mind.

    Glad you put it together. Very nice. We used a free SQLSkills plugin but now use SSMS2106 all the time and and not compatible…and it didnt work great anyway.

    • Hey, thanks, so happy you like it. I wrote it for an environment with a large/volatile number of logins and relatively few machines. Having all the logins synced at any given time without a need for manual actions was key there. Anyway, good thing you adapted it for your needs.

      If you wouldn’t mind, I’d be glad to update the code with your changes. If you want, you could e-mail me the modified proc at hello@strd.co.

  6. This is a fantastic script that I have used often! I came across two situations where the ID was not exactly replicated and I’m wondering if you could help or have seen this before.
    1) When an ID on the primary has MUST_CHANGE turned on it does not get replicated when the ID is created on the secondary. I have found that I cannot include MUST_CHANGE with the HASHED command.
    2) The GRANT IMPERSONATE does not get replicated when an ID gets created on the secondary.
    Ex. GRANT IMPERSONATE ON LOGIN::[Login1] TO [Login2] AS [Login1];

    If you have any suggestions for these scenarios, it would be of great help.

Let me hear your thoughts!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s