Sometimes you want to make changes across all tables in a particular database.

For example, lets say you’ve got a database that was replicated and now its not and you want to strip the msrepl_tran_version column. During the replication process a column is added to all tables to ensure that there is uniqueness across all the rows. When replication is stopped there  is no longer a need to have this column, in fact its confusing being there  because it doesn’t do anything. The T-SQL below will generate script which when run in a query window will alter all the table in a particular database – it will drop the msrepl_tran_version to be precise,

USE <<DATABASE>>
SELECT
 'ALTER TABLE [' + o.[name] + '] DROP COLUMN [msrepl_tran_version];'
FROM
SYS.OBJECTS AS o
INNER JOIN SYS.COLUMN AS c
ON o.OBJECT_ID = c.OBJECT_ID
AND [name] = 'msrepl_tran_version'
WHERE
o.type = 'u'

 

 

Nice and quick this one . . . .

The T-SQL below brings together a number of meta tables in SQL to present all the permissions all users have against every schema and table/column in a database.

 select prin. name, prin .type_desc, per.class_desc , per. permission_name, per.state_desc, sch.name
from sys .database_principals as prin
inner join sys. database_permissions per
on prin. principal_id = per .grantee_principal_id
       left outer join sys.schemas sch
       on per. major_id = sch .schema_id and prin. name <> 'public'
where  class_desc = 'SCHEMA'
union
select prin. name, prin .type_desc, per.class_desc , per. permission_name, per.state_desc, isnull( obj.name , 'database_name' )
from sys .database_principals as prin
inner join sys. database_permissions per
on prin. principal_id = per .grantee_principal_id
       left outer join sys.objects obj
       on per. major_id = obj .object_id
where class_desc <> 'SCHEMA' and prin. name <> 'public'
order by prin.name

From MS SQL 2008 DBAs can make use of SQL Server Audits. This feature allows you to audit the interactions against objects in your system. For example, a SQL audit could be used to monitor SELECTS, INSERTS, DELETES and UPDATES against a particular database.

Below is an example of the T-SQL required to perform an audit against a whole database

1) First create the audit against the MASTER database.

USE MASTER
CREATE SERVER AUDIT TEST_AUDIT
TO FILE ( FILEPATH = '<<PATH_TO_AUDIT_FILE>>' )
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, STATE = ON)

Check in the Security –> Audit you’ll see the audit.

2) Against the database you want to audit create the audit specification

USE <<database>>

CREATE DATABASE AUDIT SPECIFICATION TEST_AUDIT_SPECIFICATION
FOR SERVER AUDIT TEST_AUDIT
ADD (SELECT
ON DATABASE ::<<database>>
BY PUBLIC )
WITH (STATE = ON)

Check in the  <<database>> –> Security –> Database Audit Specification you’ll see the audit.

A file (with the extension .sqlaudit) will be created to at the location you specified with the audit data. T-SQL can be used to view the contents of this file.

SELECT session_server_principal_name, database_name, object_name, statement, event_time
FROM sys .fn_get_audit_file ('<<PATH_TO_AUDIT_FILE>>' ,default, default)

 

First set the database in question to single user to ensure no other connections are made to it

Then examine sys.database_files to get the file names of the database. Make a note of these names

USE <<database>>
select *
from sys .database_files

Detach the database using sp_detach_db.

EXEC sp_detach_db '<<database_name>>', 'true'

Refreshing the databases tab in SSMS will show the database disappear. It has not been deleted rather detached from management studio.

Now move the MDF and LDF files to your desired location.

Attach your database files using sp_attach_db.

EXEC sp_attach_db @dbname = N'<<database_name>>',
    @filename1 = N'M:\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Data\database_DB.mdf',
    @filename2 = N'M:\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Data\database_DB_log.ldf'

 

Refresh databases and you’ll see your newly moved databases appear in SSMS.

 

 

Lets say you’ve changed the name of your physical server and you want your sql server instance name to reflect this.

--THIS WILL SHOW YOU THE SERVERS 
select *
from sys .servers

--THIS WILL DROP THE SERVER FROM THE SERVER TABLE 
exec sp_dropserver 'OLD_SERVER_NAME'

--THIS WILL ADD THE NEW SERVER TO THE SERVERS TABLE, LOCAL WILL MAKE THIS SERVER_ID 0, THE INSTANCE YOU'RE ON
exec sp_addserver 'NEW_SERVER_NAME', 'local'

 

It goes without saying, be careful. Think carefully about what you’re dropping and adding 😉

 

Like the previous post (https://www.electricwaffle.co.uk/2013/02/using-t-sql-to-find-whos-in-what-database-role/) but this time with server roles 😉

SELECT p. NAME,m. NAME
FROM sys .server_role_members rm
JOIN sys .server_principals p
ON rm. role_principal_id = p .principal_id
JOIN sys .database_principals m
ON rm. member_principal_id = m .principal_id

 

This will return the principle against the server role they have.

 

 

 

Yes, you could go to the GUI but the T-SQL can be quicker.

use <<database>>

SELECT p. NAME
,m. NAME
FROM sys .database_role_members rm
JOIN sys .database_principals p
ON rm. role_principal_id = p .principal_id
JOIN sys .database_principals m
ON rm. member_principal_id = m .principal_id

 

This will return all the principles associated to the database in question against what role they have.

 

When examining the contents of sys.sysprocesses one of the very last columns in the table is labelled as the sql_handle. This is a very useful hex number which represents the T-SQL which was run during a process. We can use the inline table value expression dm_exec_sql_text() to translate this hex into text showing what T-SQL was run.

Very simply,

select *
from sys .dm_exec_sql_text( <<sql_handle goes here>> )

Its also possible to use dbcc inputbuffer with the spid from a process,

dbcc inputbuffer (<<spid goes here>>)

This will also return the T-SQL run but there is a limit to how many characters inputbuffer can display. If the command is long the end will be cut off.

Using T-SQL to ascertain the sizes of all the databases in an instance is really easy. The T-SQL below will show the sizes all of the databases at once in MB. This includes the sizes data and the log files combined for all databases.

SELECT DB_NAME(database_id) AS DatabaseName,
sum((size*8)/1024) as SizeOfDatabase
FROM sys.master_files
group by database_id

If you want to see all the database log file sizes

SELECT DB_NAME (mf . database_id) AS DatabaseName , sum ((size * 8)/ 1024 ) as SizeOfDatabase
FROM sys .master_files as mf
WHERE mf. type = 1 -- 1 refers to log
group by mf.database_id

If you want to see all the database data file sizes

SELECT DB_NAME (mf. database_id) AS DatabaseName, sum((size *8)/ 1024) as SizeOfDatabase
FROM sys .master_files as mf
WHERE DB_NAME (mf. database_id) in ( select d. name from sys.databases as d )
and mf. [type] <> 1 -- NOT 1 refers to log, anything not 1 is not a log
group by mf.database_id

Easy!

Classic issue – a database has (for whatever reason) been set to single-user and the one session available is being used . . .But now you need to free that session and/or make the database multi-user again.

Trying to free the session using the database properties in the SSMS GUI won’t work, you don’t have access to that session. Make life easier by making use of T-SQL.

First, run SP_Who. This will produce a list all the active connections to all databases.  Note the session ID of the active connection to the database you’re interested in.

Second, run the  KILL <<SESSION ID>> to kill the connection made by that pesky user.

The session for the database is now free, another user can access the database.

If you wish to put the database  in multi-user mode (so more than one person can access the database at a time), run the following

ALTER DATABASE <<DATABASE NAME>>
SET MULTI_USER

The following will roll back and transactions which the user may have made immediately.

ALTER DATABASE <<DATABASE NAME>>
WITH ROLLBACK IMMEDIATE 
SET MULTI_USER

To put the database in to single user mode replace the MULTI_USER with SINGLE_USER.