Tag: tips

  • I Built Another Thing! (To extract Slack conversations for my “AI memory”)

    I Built Another Thing! (To extract Slack conversations for my “AI memory”)

    I’ve written about my AI memory system before, which gives Claude a “deeper” memory across conversations. I rely on this every day, but noticed that it still misses some context due to the fact that so many day-to-day conversations happen in Slack.

    Stuff like design discussions, technical opinions, those crucial “oh by the way” messages that reshape entire roadmaps, etc.

    But Slack is surprisingly lacking in terms of smart data export or analysis. For example, we have an internal helpdesk type of channel which I wanted to get a dump of data from to analyse; things like request count trends, documentation gaps, underserved teams, etc – but no luck, even when I requested this data through proper internal channels (i.e. IT).

    Anyway, I needed something that could grab specific conversations, preserve the context, and output clean markdown that my AI assistant could digest. So I built “SlackSnap”. 🎉

    Starting Simple-ish

    The good old “copy/paste” doesn’t really work here (no threads, messes up formatting, etc), so I didn’t start that simple.

    First I tried a javascript snippet that grabbed textContent from message elements. It kinda worked, but:

    • Slack’s DOM is a maze of virtual scrolling and dynamic loading, and the last time I pretended to be a “web developer” was in the 90s 🧓
    • Only captured visible messages (maybe 20-30 out of hundreds)
    • Lost all formatting (code blocks became walls of text)
    • No thread support
    • Usernames were just IDs like “U01234ABCD”

    So I rebuilt it as a proper Chrome extension. This gave me:

    • Background service workers for file downloads
    • Content scripts with full DOM access
    • Storage API for configuration
    • Proper permissions for Slack domains

    But the real breakthrough came when I discovered Slack loads its API token into localStorage. Instead of scraping the DOM, I could use Slack’s own API (well… *I* didn’t discover shit, the nice AI in Cursor informed me that this might be a better option 😄)

    Next: Dual Extraction Methods

    SlackSnap uses a two-pronged approach:

    Method 1: API-Based Extraction (Primary)

    // Get auth token from Slack's localStorage
    const config = JSON.parse(localStorage.getItem('localConfig_v2'));
    const token = config.teams[teamId].token;
    
    // Fetch messages via conversations.history
    const messages = await fetch('/api/conversations.history', {
      method: 'POST',
      body: new URLSearchParams({
        token: token,
        channel: channelId,
        limit: '100',
        oldest: oldestUnix
      })
    });

    The API approach is nice and simple (and understandable!) because it:

    • Gets ALL messages in the specified time window, not just visible ones
    • Includes thread replies with conversations.replies
    • Provides consistent data structure
    • Works with Slack’s pagination

    But the user IDs problem remained. Slack returns messages like:

    {
      "user": "U123ABC",
      "text": "Should we refactor the auth service?",
      "ts": "1753160757.123400"
    }

    Smart User Resolution

    Instead of fetching ALL workspace users, which the AI did initially, and *I* actually corrected (chalk 1 up for the humans!), SlackSnap:

    1. Extracts unique user IDs from messages
    2. Includes @mentions from message text
    3. Fetches ONLY those specific users
    4. Builds a lookup map for the export
    // Extract user IDs from messages and mentions
    const userIds = new Set();
    for (const msg of apiMessages) {
      if (msg.user) userIds.add(msg.user);
      // Extract @mentions like <@U123ABC>
      const mentions = msg.text.match(/<@([A-Z0-9]+)>/g);
      // ... collect those too
    }
    
    // Fetch only needed users (e.g., 15 instead of 5000)
    const userMap = await fetchSpecificUsers(Array.from(userIds), token);

    Method 2: DOM Fallback

    If API access fails (permissions, network issues), SlackSnap falls back to enhanced DOM scraping:

    // Scroll to load message history
    const scrollContainer = document.querySelector('.c-scrollbar__hider');
    
    for (let i = 0; i < 20; i++) {
      scrollContainer.scrollTop = 0;
      await new Promise(resolve => setTimeout(resolve, 1500));
    
      // Check if new messages loaded
      const currentCount = document.querySelectorAll('[role="message"]').length;
    
      // ... break if no new messages after 3 attempts
    }

    This bit never worked as well (still had issues resolving user names, was more inconsistent with the scrolling, etc) so I may just remove it entirely since I’ve found the API method actually works more reliably.

    The Output: Clean, Contextual Markdown

    SlackSnap produces markdown that preserves the conversation flow:

    # SlackSnap Export: #deathstar-review
    
    *Exported: November 28, 2024 10:45 AM*
    
    ---
    
    **D.Vader** (Today 9:15 AM):
    Team, what's this I hear about an "exhaust port" vulnerability?
    
    **Galen Erso** (Today 9:18 AM):
    Nothing to worry about; low sev vulnerability we can patch out later as a fast-follower :thumbsup: :agile:
    
    **Thread Replies:**
      - **Grand Moff T**: It's only 2 meters wide right? nobody's getting close enough to even see it! :approved:
      - **Emperor P**: Yeah, okay... its just a vent I guess, probably doesn't lead anywhere important in any case. Thanks team

    Configuration

    The options page lets you control:

    • Download directory: Organizes exports (e.g., downloads/slack-exports/)
    • Filename template: YYYYMMDD-HHmm-{channel}.md for chronological sorting
    • History window: How many days back to export (default: 7) – things get a bit funky if you download too much from a very busy channel
    • Thread inclusion: Critical for capturing full context, or to disable if you just want a high level overview
    • Timestamps: Full date/time vs. just time

    How I use the output

    The structured markdown output feeds directly into my AI context system. The way I do this isn’t to capture every single little detailed message, but to export (on a weekly basis) from a few key channels likely to contain important context, then pass all of those exports into Claude at once, asking it to write a single summary file to memory for that week, focusing on team dynamics, key decisions and technical direction, etc. 

    Then the memory system can take that Slack summary into account when I do my regular “memory updates”. So now when I start a chat in Claude Desktop, it can make use of context from meeting transcripts and documents I’ve provided, plus Slack conversations!

    For the week or so I’ve been using it I’ve noticed that it feels a little more accurate, or “connected to reality”, than it did before. YMMV.

    The Technical Stack

    Everything runs locally in your browser:

    • Manifest V3: Modern Chrome extension architecture
    • Slack’s Internal API: Already authenticated, just reuse their token
    • Chrome Downloads API: Handles subdirectories properly
    • Markdown Generation: Preserves code blocks, links, formatting

    Installation and Usage

    1. Clone from GitHub: https://github.com/dcurlewis/slacksnap
    2. Load as unpacked extension in Chrome
    3. Click extension icon on any Slack conversation
    4. Messages export to your Downloads folder

    The export captures the entire history (up to your configured limit), not just what’s on screen.

    Only tested on Chrome (but should work on Chromium based browsers, or those using the same extension architecture).

    Future Enhancements?

    • Selective date ranges: Export specific time periods
    • Multi-channel export: Batch export related channels
    • Search integration: Export search results
    • Attachment handling: Download shared files/images
    • Export formats: JSON for data analysis, PDF for sharing

    But honestly? The current version solves my immediate need so I probably won’t bother adding too many bells and whistles.

    Some Observations

    Building this revealed some interesting patterns in how we communicate:

    1. Critical decisions often happen in threads – Main messages lack context
    2. Code snippets in Slack are surprisingly common – And poorly preserved
    3. Timestamps matter more than you think – “Yesterday” is ambiguous a day later
    4. User attribution is crucial – “Someone suggested” vs. “Darth Vader suggested”

    Other observations from me, less related to this tool and more on the process of developing it; “Vibe coding” can still be infuriating, but works a lot better IMO if you provide a decent project plan at the outset. 

    I’ve seen arguments that planning time is better spent “iterating” (vibing?), but I literally spent 2 or 3 minutes prompting another AI to produce my “plan” based on my quickly thrown together requirements and limitations.

    This saved probably hours of the AI running me around in circles with “vibey” scope creep, mocked functions it thinks might be handy when you implement this amazing feature one day (that I definitely didn’t ask for), etc.

    Get It Running

    The tool is here: https://github.com/dcurlewis/slacksnap

    It’s intentionally simple – no external dependencies, no build process, just vanilla JavaScript that manipulates Slack’s own data. If you’re feeding an AI assistant with your work context, this might be the missing piece.

    Let me know if you find something like this useful, or if you have any feedback/ideas to share. 

    Cheers,
    Dave


    P.S. – Yes, I realize I’m slowly building a suite of tools to feed my AI assistant. Not sure what might be up next, yet…

  • SQL Server version information from build numbers

    SQL Server version information from build numbers

    Just a quick post in case anyone else finds this useful; for a report I’ve created, I wanted to have SQL Server versions displayed in a nice, readable form, rather than the usual build number format (e.g. 11.0.3128.0).

    Server dashboard report

    So I found the very useful SQL Server Builds page (thanks to whoever maintains that), and proceeded to write a function which accepts the build number, and spits out a friendlier “version string”, down to the service pack and cumulative update level of resolution. (I didn’t need anything lower than that, but you could easily add it in yourself).

    Here’s the file, feel free to download, use, hack to pieces, etc. 😉

    function

    Cheers,
    Dave

  • Customising SQL Management Studio 2012

    I’m setting up my new work laptop, and decided to have a look around the net for what the cool kids were doing these days as far as customising SQL Management Studio 2012.  Surprisingly not a lot seems to have changed (i.e. changing font, adding keyboard shortcuts, and using a trendy dark theme are still the most common tweaks).

    Aesthetics

    So I found a dark theme that I actually might give a chance (I’ve tried a few, but always seem to revert to the default after a few hours).  I’m still not sold on the dark development environment – but there are certainly enough colour combinations available from sites like http://studiostyl.es/.

    SQL Server Management Studio 2012

    Productivity

    I also added my usual battery of keyboard shortcuts; sp_helptext, sp_whoisactive, and a wrapper script that I unimaginatively call sp_helpindex2 (which auto-detects the version of SQL running, and then calls the corresponding version of Kimberly Tripps improved sp_helpindex proc).

    Some other minor tweaks include scripting options (Tools -> Options -> SQL Server Object Explorer -> Scripting), for example adding descriptive headers, checking for object existence, etc.

    There are loads of other tweaks you can make – do yourself a favour and take the time to read through every page of options… I’m sure you’ll find more ways of improving your environment.

    Templates

    I then needed to get my templates setup (which, as I detailed in my previous post, I use to store frequently used scripts and code snippets).  This takes a little jiggery-pokery since SSMS doesn’t natively let you specify a new location for these files.

    What you need to know before starting is how Management Studio handles these template directories; in the following default path, C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems, there will be a directory called “SQL” which contains all the default templates.

    SSMS Template Directories

    When you open the templates pane in SSMS it compares the contents of this directory to your user template directory (e.g. C:\Users\username\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\Templates, or explorer window #2 in the screenshot above), and “syncs” up any differences.  I’m sure this is over-simplifying it, but it’s a good-enough explanation for the purposes of this post. Window #3 shown above is the directory containing my custom scripts library in my Sky Drive folder.

    So what you want to do is automagically “replace” the contents of #1 with #3 – SQL will take care of keeping #2 in sync (I hope…).

    To do this you need to create a symbolic link from #3 to #1.  To learn about symbolic links, read this.  They are essentially shortcuts… but on steroids.  Here’s the command I use to setup my environment:

    cd "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems"
    ren Sql Sql_Old
    mklink /D Sql "C:\My Stuff\SkyDrive\Scripts\SQL\SSMS_Templates\Sql"

    Obviously your paths (and username) will differ, so make the appropriate changes before trying this yourself.  Please be sure about what you’re doing (never just run something from the Internet without 100% understanding what it does). I take no responsibility if you somehow manage to brick your system – YMMV, etc, etc.

    All going to plan, you’ll now have SSMS using your own source of templates. 🙂

    Cheers,
    Dave

  • What’s in your toolbox?

    Toolbox Lock - by Jenn Durfey

    I’ve had a few weeks off work recently, during which time I decided to tidy up (or attempt to anyway) my collection of “utility scripts”.  This consists of about 95% regular T-SQL scripts, with the rest being a mixture of PowerShell scripts, batch files, etc.

    This made me wonder how other people managed their collections, and whether their are better ways (or tools) to catalogue and easily retrieve useful scripts and snippets?

    What I do

    My solution is to use the built in SQL Server Management Studio Template Explorer.  I found a few good blog posts explaining where the templates are stored, and some different ways of getting the most out of them (for example, I went down the route of creating symbolic links so I could have one central set of scripts stored on my Skydrive, which I have access to from any computer).

    I was thinking about taking this one step further; I’m the database team lead at work, and it might be useful to collaboratively compile a list of scripts that each of us find useful/indispensable, which we could then add to our source control repository.  We could then each have symbolic links to this repository on our own computers, meaning the whole team would automatically have access to the latest versions of our “über team toolbox” scripts.

    What’s in my toolbox?

    My SSMS Templates

    I’ve got a bunch of stuff… and I’ve still got a lot to categorise and move into my templates folder, but it’s a mix of some pretty well known utility scripts from some equally well known “SQL personalities”, as well as a lot of stuff I’ve written over the years and tucked aside for a rainy day, or scripts that I regularly need for the applications that we support.

    My “well known” utility scripts currently include:

    I have others that fall into this category (e.g. some queries from Glen Berry) that I’m still tidying up and organising.  The SQLServerPedia site also has a list of useful scripts which I’ve bookmarked for later follow-up.

    What else is there?

    Well, if you don’t think using templates is going to work for you, then there are a few other options (although not as many as I would’ve thought – hence the reason for this post)…

    Snippets

    I think of snippets as mini, or “in-line” templates.  The reason being most snippets are fragments of a larger script – for example you can have a snippet to insert a common stored procedure header block that you always use, or the syntax for a cursor declaration.  My templates tend to be more full-featured scripts, but I’m sure these could be stored as snippets too.  Here’s an article on snippets, including how to create your own.

    Talking about snippets – it’s easy to confuse the built-in SSMS snippets (which is what I was talking about above) with those included in Mladen Prajdić’s very cool SSMS Tools Pack add-in.  In SSMS Tools Pack they’re more like fully customisable shortcuts to code snippets.  This means you don’t need to have an extra window open to select the snippet, you can just (for example) type ssf and hit enter, and it expands out to SELECT * FROM automagically. 😉

    SQL Scripts Manager

    This is a free tool from Red Gate, which to be honest I haven’t played with very much yet (it’s still on my to-do list).  It seems to offer some cool features (like tagging scripts, a list of authors, etc), and comes bundled with a range of very useful scripts to get you started.  Here’s a write-up on it.

    I’ll definitely take a closer look at it when I get a chance – I’m interested to see where it stores the scripts (i.e. whether I can plug it into a central source control repository), and also whether there is any SSMS integration (which would be way cooler than just an external app).

    Good ol’ Explorer

    I’m guessing this is still the most common method; just putting a bunch of snippets and scripts into a directory (or, if you’ve got some semblance of order, perhaps a categorised series of directories).

    If this works for you (as it did me for many years), and you can still easily find what you need, then I guess there’s no real reason to change. 😉

    What do you use?

    So, what method do you use to keep your scripts library in order, and do you have any other cool scripts you’d recommend I add to my library?  I’d love to hear from you via the comments section below.

    Cheers,
    DB Dave

  • CURSORs and WHILE-Loops

    can of wormsYes, I know this is a giant (some might say pointless, and I wouldn’t totally disagree) can of worms I’m opening here, but stay with me for a minute

    Why?

    We were discussing this at work a few months ago, and I was one of those people who followed “conventional wisdom” which says that SQL cursors are evil, and should be replaced with WHILE-loops if a set-based alternative isn’t possible (or practical).

    So I thought I’d do some testing for myself.  I know this has been done more than once before (I won’t mention any articles specifically, but they’re easy to find using your search engine of choice) – but I like to figure things out for myself rather than read a blog and take the author’s word as infallible.  Some of the other tests I found online also only concentrated on one specific metric when measuring performance (e.g. physical I/O), or were too obviously biased for my taste.

    Living in the Real World

    There are also what I refer to as “non-real-world” arguments against one or the other, for example:

    • Cursors are best because they’re easier/quicker to write (usually less variables and/or temp tables required).
    • While-loops are best because they’re inherently faster, since they don’t have to make use any of the internal mechanisms that cursors use to allow backwards and forwards data-set traversal.

    The reason I call these “non-real-world” problems, is that I don’t think any DBA or developer worth their salt is going to worry too much about how many more characters one takes to type than the other, and I’ve very seldom (if ever) needed to use anything but a simple forward-only cursor – so these problems aren’t representative of the majority of use-cases in my experience.

    Obviously if you take something like this super-simplified while-loop below, it would be difficult to write a cursor that would perform faster – but why the hell would you?

    DECLARE @i INT = 0;
    WHILE @i <= 1000000 SET @i += 1; -- 405ms on my laptop

    What?

    Alright, down to business. Using the AdventureWorks database on my laptop (running 2008R2 build 10.50.2500.0), I wrote a simple test script which iterates through each SalesOrderHeader row (for online orders) and counts the number of SalesOrderDetail rows for each SalesOrderID.  I know… for someone who was just talking about “non-real-world” examples, this is pretty unrealistic – but the point is that it generates a measureable amount of I/O and CPU load, and I can reproduce the logic easily using both a while-loop and a cursor.

    Here’s the code for the while-loop test:

    ------------------------------------------------
    -- WHILE LOOP (using counter)
    ------------------------------------------------
    CHECKPOINT;
    DBCC DROPCLEANBUFFERS;
    GO
    SET NOCOUNT ON;
    GO
    IF OBJECT_ID('tempdb..#SOID') IS NOT NULL DROP TABLE #SOID;
    CREATE TABLE #SOID (ID INT IDENTITY, SalesOrderID INT NOT NULL);
    DECLARE @i INT = 1, @ii INT, @SalesOrderID INT, @LineCount INT;
    
    INSERT #SOID (SalesOrderID)
    SELECT SalesOrderID
    FROM Sales.SalesOrderHeader
    WHERE OnlineOrderFlag = 1;
    
    SET @ii = @@ROWCOUNT;
    
    WHILE @i &amp;lt;= @ii
    BEGIN
    SET @SalesOrderID = (SELECT SalesOrderID FROM #SOID WHERE ID = @i);
    
    SET @LineCount = ISNULL(@LineCount, 0) + ISNULL((
    SELECT COUNT(*)
    FROM Sales.SalesOrderDetail
    WHERE SalesOrderID = @SalesOrderID
    ),0);
    
    SET @i += 1;
    END
    IF @LineCount &amp;lt;&amp;gt; 60398
    BEGIN
    RAISERROR('Bad Total',16,1);
    PRINT @LineCount;
    END
    GO

    Even for a simple while-loop there are multiple ways to write the logic – I’ve chosen a simple counter here, but didn’t see massive variances in the performance of the 2 or 3 methods I tested.

    Here’s the code I used to test cursors:

    ------------------------------------------------
    -- CURSOR
    ------------------------------------------------
    --DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
    -- [ FORWARD_ONLY | SCROLL ]
    -- [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
    -- [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
    -- [ TYPE_WARNING ]
    CHECKPOINT;
    DBCC DROPCLEANBUFFERS;
    GO
    SET NOCOUNT ON;
    GO
    DECLARE @SalesOrderID INT, @LineCount INT;
    DECLARE SOID CURSOR TYPE_WARNING
    FOR
    SELECT SalesOrderID
    FROM Sales.SalesOrderHeader
    WHERE OnlineOrderFlag = 1;
    
    OPEN SOID;
    FETCH NEXT FROM SOID INTO @SalesOrderID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @LineCount = ISNULL(@LineCount,0) + ISNULL((
    SELECT COUNT(*)
    FROM Sales.SalesOrderDetail
    WHERE SalesOrderID = @SalesOrderID
    ), 0);
    
    FETCH NEXT FROM SOID INTO @SalesOrderID;
    END
    CLOSE SOID;
    DEALLOCATE SOID;
    IF @LineCount &lt;&gt; 60398
    BEGIN
    RAISERROR('Bad Total',16,1);
    PRINT @LineCount;
    END
    GO

    This represents the simplest test (i.e. just a plain cursor declaration with no options other than TYPE_WARNING).  I used this as a baseline, to which I added various combinations of cursor options, such as STATIC vs DYNAMIC, LOCAL vs GLOBAL, etc.

    To measure performance I profiled the execution of the main batch (i.e. not including the checkpoint, clearing the caches, etc) and recorded CPU, Duration, Reads, & Writes over 5 executions per test run (I actually ran each more than just 5 times, but results were so consistent that I only recorded the last 5 executions of each test).

    Show me the money

    Cursor DurationThe surprising thing for me (in this test case) was how poorly the while-loop performed.  The graph to the right shows the various cursor-based test runs, all of which completed in less than 1 second (click the image to viewer a larger copy).

    The while-loop consistently took 30 seconds to run.

    By the way, please feel free to point out any mistakes I’ve made.

    Average ReadsThe reads show a similar story regarding the stark difference between the while-loop and any of the cursor tests.  The cursor read I/O figures are in the graph to the right, and the while-loop averaged well over 1.8 million reads (it dwarfed the others on the graph so I removed it).

    I guess what I find interesting is that many people have run tests, and look at logical or physical I/O, but don’t take into account CPU or overall duration.  If my data is sitting in memory anyway, I don’t really care about those logical reads – but the difference between a query taking 100 vs 1000 ms of my CPU’s time is very important to me.  Am I neglecting to take anything else (important) into account?

    Conclusion

    I don’t have one…

    These tests are too fickle to make any concrete observations – you could change just a few lines of code and get completely different results to what I’m seeing.

    Personally, I will continue to use both where appropriate.  Before anyone has a heart attack let me clarify that I would always first use a set-based solution before resorting to either.

    But… my rule of thumb is that I use CURSORS if I’m performing data manipulation, and a WHILE loop if I’m performing an action until some condition is met.  For cursors, I tend to use the “LOCAL STATIC FORWARD_ONLY READ_ONLY” options, just because they roll off the tongue so nicely. If you’re on the SQL Server team at Microsoft and you’re reading this – in the next version of SQL could you please consolidate these options down to a single “GO_BABY_GO” option instead? Winking smile

    As a final note, highlighting why a set-based alternative is always better; running this query “properly” (i.e. set-based, not RBR) gave me just over 900 reads, and completed in 29 milliseconds. No contest really.

    Cheers,

    Dave

  • T-SQL Tuesday #21 – Temporarily permanent hacks

    This month’s T-SQL Tuesday Wednesday is hosted by Adam Machanic (blog|twitter) – who in fact started the T-SQL Tuesday concept himself back in 2009 (read more about the genesis here).

    T-SQL Tuesday #21This month’s it’s about “crap code”.
    We’ve all seen it, and we’ve all written our fair share of it.  I’m not going to concentrate too much on actual crappy code – like using deprecated syntax or functions, lots of GOTO logic within cursors (always bags of fun), etc. – but will rather look at some of the other steaming piles of “legacy” we leave behind us in the course of our busy DBA lives. None of these is necessarily going to take your production system down, or cause world war 3, but they’re bloody annoying at least.

    Some of my favourite examples of such landmines include;

    1. “Temporary” logging tables
    2. Renamed or “backup” tables
    3. Hard-coded values in stored procedures
    4. Scheduled job sprawl (or bloat)
    “I’ll just add some logging while I troubleshoot this bug.”

    Take one stored procedure, add a handful of intermittent business rule bugs, and you’ve got the perfect excuse to add a new logging table, and some logging code to the procedure. Something similar to this perhaps:

    create table dc_logging_tmp (dt datetime, m_id int, bal money);
    
    IF @member_id IN (6763, 3884, 8734) AND @ledger_balance < 0
    INSERT dc_logging_tmp (m_id, bal) VALUES(@member_id, @ledger_balance);

    Of course the table name is generic, there are no indexes, and the logging code has no comments explaining its existence – but you’ll only need it for a few hours, maybe a day or 2 at most, and then you’ll rip it all out again. Right?

    If you really only need something short-term, and there’s a chance you won’t remember to tidy up after yourself (and it won’t break anything if the table doesn’t exist), then consider creating your logging objects in TempDB.  Next time you restart your SQL service: Poof!! All gone.

    “Just to be safe I’ll create a copy of this table before we deploy this application update.”

    You’re about to do a deploy to production which involves making some data changes. So, being the sensible, risk-averse DBA that you are, you decide to take a snapshot of the current table and do a simple “SELECT * INTO” before the update.

    Of course you’ll remember to drop this temporary backup table tomorrow once you’re sure you won’t need to roll back the update, so calling it [zzz_table_name] seems reasonable. Right?  Aaah, no. There’s nothing stopping you from creating table with names like [ledger_pre_Jul2011_v3_deploy_drop_after_3_weeks].
    Okay, that might be extreme, but you get the idea.  If you feel like being especially thorough you could even make use of extended properties to add comments, descriptions, or whatever else you like.

    CREATE TABLE TableWithDescription (col INT)
    GO
    
    EXEC sp_addextendedproperty
    @name = N'Description',
    @value = 'This table is for XYZ, blah blah blah, etc...',
    @level0type = N'Schema', @level0name = 'dbo',
    @level1type = N'Table', @level1name = 'TableWithDescription';
    GO
    
    SELECT name, value
    FROM fn_listextendedproperty (
    'Description', 'Schema', 'dbo', 'Table',
    'TableWithDescription', NULL, NULL
    );
    GO

    Here’s a little more information on extended properties from Glenn Berry (twitter|blog).

    “It’ll just be easier to hard-code these hourly rate values in this procedure.”

    The thing about writing maintainable code is that it should be easy to maintain (the clue is in the name, you see). You shouldn’t need to alter a dozen stored procedures if your company’s rates change – things like that should be stored in a lookup table, or (an option I quite like) in table valued functions or views. You should obviously still test and choose what’s best suited to your particular situation.

    The main advantage of using a TVF or view, in my opinion, is that you can make use of source control and versioning (which is more difficult to do with data stored in a table). This article by Joe Celko gave me a few new ideas in this regard.

    Unfortunately I still find plenty of examples of developers including static values in stored procedures. Remember kids; every time you get lazy and hard-code something that you shouldn’t, a kitten loses it’s wings.

    “I need this stored proc to run every day. Could you just quickly create a scheduled job for me?”

    How many scheduled jobs do you have? Do you know exactly what every single one of them is doing? If you do, then I’m jealous.

    Environments that have been around for a while tend to suffer from scheduled job build-up.  This can rear its head in one of two ways; either you end up with a million separate jobs (i.e. job sprawl), or a few jobs, each with a hundred steps (i.e. job bloat).

    My advice in combatting this is to decide how you’re going to group your jobs (e.g. by schedule, function, target database, etc), and then stick to it.  Also remember to religiously maintain your job names and descriptions, schedule names, and step names – this makes your jobs essentially self documenting (you could even write a few simple queries to actually generate some documentation for you!).

    Well, that’s about enough from me on the topic of crap. I’d love to hear your feedback on any of the examples I’ve covered here, and feel free to tell me about your own crap (or the crap you’ve had to deal with).

    Cheers
    DB Dave