The APP_NAME function returns the name of the application for the current database connection if the application has set it. Run the following in SSMS; ?View Code TSQLSELECT APP_NAME(); This will return; Microsoft SQL Server Management Studio – Query There’s no method of setting this value within TSQL. To set this value you need to [...]

I’m moving the backup jobs we run onto specific users and need to assign the db_backupoperator role to the user for each database. Very tedious to do in SSMS so here’s a quick script I knocked up. The script will assign a specific database role to all databases on a SQL Server. The user will [...]

A while ago Thomas LaRock (blog | twitter) posted a script that used sysusers and the sp_helpusers proc to audit user groups setup in your database. The original post is here. I’m busy documenting my environment and thought this would be a great addition to the info I collect. The only issue I had with [...]

We’re currently busy reviewing some of the historical database design decisions taken in our organisation. We’ve noticed quite a lot of columns, that are specified as nullable, but do not actually contain any nulls. Obviously this fact makes the column a possible candidate for changing to NOT NULL. I wanted to make this task a [...]

In order to automate testing of backups it’s useful to be able to query backup files to access various bits of meta-data. We can do this with the RESTORE FILELISTONLY TSQL command. In the simplest format the command is as follows; ?View Code TSQLRESTORE FILELISTONLY FROM DISK = ‘c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\AdventureWorks_20110320.bak’; This doesn’t help [...]

I was Googling around the other day for a bit of TSQL to quickly grant a user permission to use all procedures and functions and came across the following post. We have a few table -valued functions in our system so this script buckled with the following error; Msg 4606, Level 16, State 1, Line [...]

I’ve been spending the past week or so changing a colleagues scripts into SSRS reports so we can automate things a bit better. During some QA checks I noticed that I was coming out with higher counts on one section of the report. Much to my frustration the two queries looked identical and I had [...]

I’ve noticed people before struggling using sp_rename with tables that aren’t in the default schema. Many people don’t use schemas, so there’s often confusion, when they finally do come across the need to rename a table belonging to another schema. Assuming the below ‘Suppliers’ table is in the users default schema (usually dbo) then the [...]

I was recently given a du-duping task which was much more difficult than I anticipated and taxed my SQL brain to its limits. I thought of using a CTE to do this but all of the examples I could find for deleting records with a CTE wouldn’t have worked in my situation. Essentially the table [...]

Today a colleague was running into some difficulties matching football team names containing accented characters. For example Olympique Alès and Olympique Ales were not matching when he wanted them to. The issue here is all to do with collations. We can use the Latin1_General_CI_AI collation in our queries to force the comparison to ignore accents [...]