Skip to content

2013🔗

  • 1 min read

Renaming all references inside stored procedures and functions can be migraine worthy without a little help...

{{< admonition type="info" title="Updated: 2016-03-18" >}} Cleaned up formatting. This is older code limited to procs and functions. I'm sure there is a better way to do this now, but leaving here as it might help someone else in the meantime. {{< /admonition >}}

If you run across migrating or copying a database structure for some purpose, yet need to change the database references or some other string value inside all the procedures and functions to point to the newly named object, you are in for a lot of work! I built this procedure to search all procedures and functions, and script the replacement across multiple databases, to streamline this type of conversion.

I'll post up one for views and synonyms later, as my time was limited to post this. In my case, this script was built to replace DB1 with DB2, and I had to accomplish this across several databases at once.

This script might help save you some time!

  • 1 min read

Shortcut to reference examples, syntax, and definitions straight from SSMS

I've never really used the F1 key for help files with most applications. I was surprised at the usefulness in SSMS I discovered today that uses scripting to actually get you MSDN articles relevant to your current selection in the query editor.

If you have a keyword selected and want to view details, definition, and examples on it, you can highlight the phrase or select the word, press F1, and SSMS will pull up the appropriate MSDN article. The only issue I ran into was that it pulls up the most recent article, so if you aren't running SQL Server 2012, make sure you select the < 2012 documentation to be sure it is accurate.

f1_shortcut_query

f1_shortcut_results

  • 2 min read

Check Constraints can help enforce the all or nothing approach when it comes

If you have a set of columns inside your table that you want to allow nulls in, however if one of the columns is updated force all columns in the set to be updated, use a check constraint. In my case, I had 3 columns for delete info, which were nullable. However, if one value was updated in there, I want all three of the delete columns to require updating. I created the script below to generate the creation and removal of these constraints on a list of tables:

/* CHECK CONSTRAINT TO ENSURE SET OF COLUMNS IS NULL OR IF UPDATED,
THAT ALL COLUMNS IN SET ARE UPDATED Columns: delete_date null delete_by_id null delete_comment null PASS CONDITION
1: IF ALL COLUMNS NULL = PASS PASS CONDITION
2: IF ALL COLUMNS ARE UPDATED/NOT NULL = PASS

FAIL: IF 1,2 OF THE COLUMNS ARE UPDATED, BUT NOT ALL 3 THEN FAIL
*/

/* GENERATE CHECK CONSTRAINT ON ALL SELECTED TABLES TO REQUIRE ALL DELETE
DATE COLUMNS TO BE UPDATED CORRECTLY */

select
    t.TABLE_SCHEMA
    ,t.TABLE_NAME
    ,script_to_remove_if_exists = ' IF exists (select * from sys.objects where name =''check_' + t.TABLE_SCHEMA + '_' + t.TABLE_NAME + '_softdelete_requires_all_delete_columns_populated_20130718'') begin alter table ' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME + ' drop constraint check_' + t.TABLE_SCHEMA + '_' + t.TABLE_NAME + '_softdelete_requires_all_delete_columns_populated_20130718 end '
    ,script_to_run =              ' alter table ' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME + ' add constraint check_' + t.TABLE_SCHEMA + '_' + t.TABLE_NAME + '_softdelete_requires_all_delete_columns_populated_20130718 check ( ( case when delete_date is not null then 1 else 0 end + case when delete_by_id is not null then 1 else 0 end + case when delete_comment is not null then 1 else 0 end ) in (0, 3) ) '
from
    INFORMATION_SCHEMA.TABLES t
where
    t.TABLE_NAME like 'mytablename%'
    and exists (select
            *
        from
            INFORMATION_SCHEMA.COLUMNS C
        where
            t.TABLE_CATALOG = C.TABLE_CATALOG
            and t.TABLE_SCHEMA = C.TABLE_SCHEMA
            and t.TABLE_NAME = C.TABLE_NAME
            and C.COLUMN_NAME = 'delete_by_id')
    and exists (select
            *
        from
            INFORMATION_SCHEMA.COLUMNS C
        where
            t.TABLE_CATALOG = C.TABLE_CATALOG
            and t.TABLE_SCHEMA = C.TABLE_SCHEMA
            and t.TABLE_NAME = C.TABLE_NAME
            and C.COLUMN_NAME = 'delete_comment')
    and exists (select
            *
        from
            INFORMATION_SCHEMA.COLUMNS C
        where
            t.TABLE_CATALOG = C.TABLE_CATALOG
            and t.TABLE_SCHEMA = C.TABLE_SCHEMA
            and t.TABLE_NAME = C.TABLE_NAME
            and C.COLUMN_NAME = 'delete_date')
order by
    t.TABLE_SCHEMA asc
go
  • 1 min read

TSQL Snippet for viewing basic info on database principals and their permissions

Quick snippet I put together for reviewing basic info on database users/principals, permissions, and members if the principal is a role.:

/*******************************************************
    Some Basic Info on Database principals, permissions, explicit permissions, and if role, who is in this role currently
    *******************************************************/

    ;with roleMembers as (
                            select
                                drm.role_principal_id
                            ,dp.principal_id
                            ,dp.name
                            from
                                sys.database_role_members drm
                                inner join sys.database_principals dp
                                    on drm.member_principal_id = dp.principal_id
                            )
    select
        db_name()
        ,dp.name
        ,stuff((
                select distinct
                    ', ' + p.permission_name
                from
                    sys.database_permissions p
                where
                    dp.principal_id = p.grantee_principal_id
                    and p.major_id  = 0
                    and p.state     = 'G'
                for xml path(''), type
                ).value('.', 'varchar(max)'), 1, 1, ''
                ) as general_permissions
        ,stuff((
                select distinct
                    ', ' + p.permission_name
                from
                    sys.database_permissions p
                where
                    dp.principal_id = p.grantee_principal_id
                    and p.major_id  = 0
                    and p.state     = 'D'
                for xml path(''), type
                ).value('.', 'varchar(max)'), 1, 1, ''
                ) as deny_permissions
        ,stuff((
                select distinct
                    ', ' + p.permission_name + ' on ' + object_schema_name(p.major_id) + '.' + object_name(p.major_id)
                from
                    sys.database_permissions p
                where
                    dp.principal_id = p.grantee_principal_id
                    and p.major_id  <> 0
                for xml path(''), type
                ).value('.', 'varchar(max)'), 1, 1, ''
                ) as specific_permissions
        ,stuff((
                select distinct
                    ', ' + r.name
                from
                    roleMembers r
                where
                    r.role_principal_id = dp.principal_id
                for xml path(''), type
                ).value('.', 'varchar(max)'), 1, 1, ''
                ) as current_active_members
    from
        sys.database_principals dp
    order by
        dp.name asc;
  • 1 min read

A moment of void in the cranium reveals a recursive computed column with an

Msg 402, Level 16, State 1, Line 67 The data types varchar and void type are incompatible in the add operator.

I came across this error today when I accidentally used a computed column in a temp table, that referenced itself. This very unhelpful message was caused by referring to the computed column itself in the computed column definition, ie typo. Beware!

  • 4 min read

On how to Googlify your SQL statements for future searching

For sake of future generations, let's begin to reduce typing and reuse code we've built. I think we can all agree that TSQL statements are often repeated. Ideally, snippets should be created to reduce repeated typing and let us focus on logic and content. However, some statements may not really be "snippet worthy", and just be quick adhoc queries. In the past, the solution for saving queries for reuse or reference in the future would be to just save in the projects folder manually. However, it is difficult to always make sure the file is saved, review previous version that may be want had overrode, or even review what statements you actually executed. SSMSToolsPack has historically offered a great logging option. However, as an individual it was hard to justify the cost out of my own pocket. SSMSBoost has provided a great solution! Note that this was recently added (April), and is a "rough draft" , with minimal interface options, yet provides an amazing solution that I've found to offer a great solution. In addition to the other great features that SSMSBoost offers (which I'll write about in the future), SSMSBoost now offers 3 unique solutions to saving work.

  1. Executed Statement Logging This feature saves all your executed statements (ie, when you hit execute) as a .sql file for future reference. As of today, there is no GUI for managing this. Never fear, I have a great solution for you.
  2. Editor History Logging This feature saves the current contents of your open query windows at predefined intervals, by default set to 60 seconds. According to their documentation, if no changes have been made to file, it will not save a new version. It will only add a new version once changes are detected to a file. ssmsBoost_findInHistory 3.Tab History Logging If you crash SSMS, close SSMS without saving tabs, or have some unsavory Windows behavior that requires SSMS to be restarted, don't fear... your tabs are saved. When restarting you can select restore tabs and begin work again. I've found this feature to be a lifesaver! ssmsBoostRecentTabs

Searching Your Executed and Editor History

Instructions I recommend for setup and searching your entire sql history nearly instantly.

  1. Install SSMSBoost (free community edition if you can't support with professional version)
  2. Install DocFetcher(open source full text search tool. Best I found for searching and previewing sql files without the complexity of using GREP or other similar tools)
  3. Download and run Preview Handler from WinHelp
  4. Run Preview Handler > Find .SQL > Preview as plain text
  5. Run SSMS > Open Settings in SSMSBoost
  6. Configure settings as you see fit. I personally move my Editor History and Executed statement's location to my SSMS Folder, so that I can use something like Create Synchronicity to backup all my work daily. SSMSBoost_settings
  7. Restart SSMS for settings to take effect.
  8. Start DocFetcher, go to settings in the top right hand corner. DocFetcher_1_startup

Basic Settings I choose (If you aren't using Bitstreamfont... you are missing out) DocFetcher_2_basicSettings

Docfetcher Advance settings tweaks Change

CurvyTabs = true HtmlExtensions = html;htm;xhtml;shtml;shtm;php;asp;jsp;sql InitialSorting = -8
  • Why? Curvy tabs... because curves are nice
  • HTML Extensions, obvious

  • Initial Sorting = -8 means that instead of sorting by "match %" which I didn't find helpful for me, to sort by modified date in desc order. This means I'll find the most most recent match for the text I'm searching for at the top of my list.

  • Setup your custom indexes. I setup separate indexes for executed statements and editor history so I could filter down what I cared about and eliminate near duplicate matches for the most part. Right click in blank space to create index.

I setup as follows: DocFetcher_createIndex1
DocFetcher_createIndex2

  1. Now the DocFetcher daemon will run in the background, if you copied my settings, and update your indexes. Searching requires no complex regex, and can be done easily with statements. I'd caution on putting exact phrases in quotes, as it does detect wildcards. DocFetcher_previewSearch
  • 1 min read

View computed columns in database

Snippet to quickly view computed column information. You can also view this by doing a "create table" script. This however, was a little cleaner to read and view for me.

select
    database_name = db_name()
    ,object_schema_name = object_schema_name( object_id )
    ,object_name = object_name( object_id )
    ,full_object_name = object_schema_name( object_id ) + '.' + object_name( object_id )
    ,column_name = name
    ,cc.is_persisted
    ,cc.Definition
from
    sys.computed_columns cc
order
    by full_object_name asc
  • 1 min read

Get synonym definitions for all databases in server

If you want to audit your enviroment to look at all your synonyms and see where they are pointing, you can use exec sys.sp_MSforeachdb to loop through databases, and even filter. It will save some coding. However, my research indicates it is probably a bad practice to rely on this undocumented function as it may have issues not forseen and fully tested.

Additionally, support may drop for it in the future. I recreated what I needed with a cursor to obtain all the synonym definitions into a temp table and display results.:

/*
    create temp table for holding synonym definitions & list of DB
    */

    if object_id('tempdb..#dblist') is not null
        drop table #dblist;
    select
        *
    into #dblist
    from
        sys.databases
    where
        name not in ('master', 'tempdb', 'model', 'msdb')
        and State_desc = 'ONLINE'
        and Is_In_Standby = 0
    if object_id('tempdb..#temp') is not null
        drop table #temp;

    create table #temp
        (
            db_name               sysname
            ,object_id             int
            ,name                  sysname
            ,base_object_name      sysname
            ,server_name_hardcoded as case
                when base_object_name like '%ThisDatabaseIsOkToHardCode%'
                then 0
                when len(base_object_name)
                        - len(replace(base_object_name, '.', '')) > 2
                then 1
                else 0
            end
        )

    go

    declare @DbName sysname
    declare @XSQL varchar(max)
    declare @CompleteSQL varchar(max)
    declare db_cursor cursor fast_forward read_only local for select
                name
            from
                #dblist
    open db_cursor
    fetch next from db_cursor into @DbName;

    while @@fetch_status = 0
    begin
        set @XSQL = '
                    insert into #temp
                    ( db_name ,object_id ,name,base_object_name )
                    select
                        db_name()
                        ,s.object_id
                        ,s.name
                        ,s.base_object_name
                    from
                        sys.synonyms s
                    '
        set @CompleteSQL = 'USE ' + @DbName
                            + '; EXEC sp_executesql N'''
                            + @XSQL + '''';
        exec (@CompleteSQL)
        fetch next from db_cursor into @DbName;
    end

    close db_cursor
    deallocate db_cursor
    go
    select
        *
    from
        #temp t
  • 1 min read

Calculating the next beginning of month and the current end of month

Handling dates is always a fun challenge in T-SQL! Finding the current end of month and next months beginning of month is straight forward, but I like to find new ways to do things that take less coding, and hate date conversions that require a lot of manipulation of characters and concatenation. This was what I came up with for avoiding character conversions and concatenation for finding the current BOM (beginning of month) and EOM (end of month) values. Adjust according to your needs. Cheers!

    --use datediff from 0, ie default 1900 date, to calculate current months as int
    declare @ThisMonth int = datediff(month,0,cast(getdate() as date))

    --add 1 to the current month to get the next month
    declare @NextBom date = dateadd(month,@ThisMonth+1,0)

    -- subtract a day from the beginning of next month to get the current end of month, without worrying about 28, 30, or 31 days.
    declare @ThisEom date = dateadd(day,-1,@NextBom)
    select @ThisMonth select @NextBom select @ThisEom