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

Moving Tempdb

You might want to move tempdb for a variety of reason, sizing being the most likely issue. Here’s how to do it using T-SQL

1) use tempdb. execute sp_helpfile to show the files associated to the database.

USE TempDB

EXEC sp_helpfile -- this will tell you want the names of the files to move are

2) Use the master database and alter the tempdb to change the location of the files

USE master

ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = '<<WHERE YOU WANT THE DATA FILE TO GO>>\tempdb.mdf')

ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = '<<WHERE YOU WANT THE LOG FILE TO GO>>\templog.ldf')

3) A message will be displayed informing us that the files have been modified in the catalogue but no changes will be made until the database is restarted.

4) Restart SQL and the changes will be made.

Obviously, make sure your restart isn’t going to affect production systems 😉

 

 

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)

 

A quickie . . .

Did you know that sp_spaceused can tell you the number of rows in a database using a lot less effort than using count()?

Count() will go through the whole table counting every row, sp_spaceused however will use meta data about the table to ascertain how many rows there are.

Might be useful 😉