Archive for February 2014

Audit database user & role mappings in SQL Server

This script provides you with a list, of the database user and database role mappings, for an entire SQL Server instance. The following system views are used; sys.database_role_members sys.database_principals ?View Code TSQLCREATE TABLE #user_groups ( username VARCHAR(50), [login_type] VARCHAR(50), database_name VARCHAR(50), database_group VARCHAR(50) );     EXECUTE sp_MSForEachDB ‘USE ?; INSERT INTO #user_groups SELECT dp.name […]

Check the value of an Environmental Variable on Multiple servers

Here’s one powershell method for how to check the value of an environment variable on multiple servers; ?View Code POWERSHELL$computers = @("server1", "server2", "server3");   foreach ($c in $computers) { Get-WmiObject -ComputerName $c -Class Win32_Environment -Filter "Username = ‘<SYSTEM>’ AND Name = ‘VAR_NAME’"; } Output will be similar to below… VariableValue Name UserName ————- —- […]

Correct a log file with too many VLFs

The what and why of this post is explained here Transaction Log VLFs – too many or too few?. Presented here is a quick practical example of how you might correct this issue in a database log file. As a first step you’ll need to get the logical name of your log file for the […]

A quick example of why IN can be bad

Here’s just a quick demo that illustrates why the IN operator in TSQL might not perform as well as alternatives (like a range query or joining onto a temporary table containing your values). ?View Code TSQLIF OBJECT_ID(’tempdb..#temp’) IS NOT NULL BEGIN DROP TABLE #temp; END;   CREATE TABLE #temp ( id INTEGER NOT NULL IDENTITY(1,1), […]

Generate PK drops and creates using TSQL

Here’s just a couple of queries I used to generate PK drops and creates using the sys.key_constraints view. I wanted to do this for a database using Poor Mans Partitioning. Generate drops… ?View Code TSQLSELECT ‘ALTER TABLE ‘ + OBJECT_SCHEMA_NAME(t.[object_id]) + ‘.’ + t.name + ‘ DROP CONSTRAINT ‘ + c.[name] + ‘;’ FROM sys.key_constraints […]