sp_MSforeachtable (Undocumented Stored Procedure)

Loops though all tables in a database, repeating the specified command on each one.

The question mark represents the table name, examples are ;

sp_MSforeachtable @command1='print ''?'' dbcc checktable (''?'')'

sp_MSforeachtable @command1='print ''?'' truncate table [''?'']'

You can issue up to three commands to the stored procedure using parameters @command1 through @command3.

sp_MSforeachdb (Undocumented Stored Procedure)

Loops though all databases, repeating the specified command on each one.
The question mark represents the database name, for example ;

EXEC sp_MSforeachdb @command1='print ''?'' DBCC CHECKDB (''?'') '

SQL 2005 : CLR - External Access permissions

Msg 6522, Level 16, State 1, Procedure SP_Backup, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "SP_Backup":
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at DatabaseName.dbo.SP_Backup(Parameter1, Parmeter2, Parameter3...)

The CLR stored procedure is using XP_CMDShell to access the file system.

The permissions on the assembly therefore need to be increased to allow this.


Changing assembly permissions in Management Studio -

1) Navigate to (databasename) > Programmability > Assemblies > (expand)
2) Double click assembly name to get assembly properties.
3) Set permission set of assembly to 'external access'.


Changing assembly permissions in tsql -

GRANT EXTERNAL ACCESS ASSEMBLY TO AssemblyName

Configuring OpenRowset / OpenDatasource

Msg 15281, Level 16, State 1, Line 2
"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. "

Like the error says, here is where to enable in SAC >

CASE Basics

-- Simple Case Statement

CASE @SecurityPass
 WHEN 2 THEN 'Admin'
 WHEN 1 THEN 'User'
 ELSE 'No Access'
END

Searched Case Statement

CASE
 WHEN @Temp > 30 THEN 'Hot'
 WHEN @Temp < 0 THEN 'Freezing'
 ELSE 'Between'
END 

Using Openrowset to return usable columns from a stored procedure

use master
go

-- drop if already present
if object_id('master..waittype_summary') is not null 
 drop proc waittype_summary

-- create procedure
create proc waittype_summary as
  select lastwaittype, 
        count(dbid) as process_count,
         sum(cpu) as cpu
    from master.dbo.sysprocesses
group by lastwaittype
order by sum(cpu) desc;
go

-- use openrowset to query the local procedure
select * from openrowset('SQLOLEDB', 'Trusted_Connection=Yes;Server=(local);Database=master', 'exec dbo.waittype_summary')
go

Although when running locally (as in this example), this is a daft idea (much easier to use a temp table) it demonstrates how to use openrowset.
Openrowset allows you to quickly connect to external data sources (access, excel, text files, oracle etc... ) without adding linked servers or importing data.
Under SQL 2005 however, we get this error -

Msg 15281, Level 16, State 1, Line 2
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

This is because SQL 2005 is more secure by default and the OpenRowset statement needs to be allowed via SAC (the Surface Area Configuration tool).

27/11/06 : More about that, here.

Using temporary tables to return usable columns from a stored procedure

use master
go

-- clearup objects
if object_id('master..waittype_summary') is not null 
drop proc waittype_summary
if object_id('tempdb..#waittype_summary_results') is not null 
drop table #waittype_summary_results
go

-- procedure to fetch results from
create proc waittype_summary as
select lastwaittype, 
count(dbid) as process_count,
sum(cpu) as cpu
from master.dbo.sysprocesses
group by lastwaittype
order by sum(cpu) desc;
go

-- temp table to store results
create table #waittype_summary_results
( lastwaittype varchar(30),
process_count int,
cpu int );
go

-- populate temp table
insert into #waittype_summary_results exec waittype_summary

-- display results
select * from #waittype_summary_results

Find tables with triggers

sql 2005 +

select 
schemas.name as schema_name,
tables.name as table_name,
triggers.name as trigger_name
from 
sys.objects tables
inner join sys.objects  triggers
on triggers.parent_object_id = tables.object_id
inner join sys.schemas schemas
on tables.schema_id = schemas.schema_id
where triggers.type_desc = 'SQL_TRIGGER'

sql 2000

select
       object_name(parent_obj) as tablename
      ,name as triggername
from sysobjects
where type = 'tr'
order by 1, 2

Linked Server : RPC error

Running a Stored Procedure on a Linked Server.

-- local server

exec sp_executesql N'select @@version'

-- remote server

exec tranasctional2005...sp_executesql N'select @@version'

" (1 row(s) affected) Msg 7411, Level 16, State 1, Line 4 Server 'tranasctional2005' is not configured for RPC. "

Right click, Linked server Name,

Select Properties.

Select Server Options page from the menu on the left.

Set RPC Out to 'True' on the pane on the right.

Shrinking Databases

This is difficult to perform without downtime as shrinking a database requires exclusive use of it.
If a database is online, the portion of the file in use is likely to be at the end of it, making reducing it's size impossible. SQL may have finished with the file and may just 'forgotten' to release this space. If this is the case, the following is worth a try.

Try the shrinkdatabase and shrinkfile methods below. They may achieve from partial to total success, depending on current activity i.e. any running processes.

Method 1 : Use the TSQL command dbcc shrinkdatabase >

i. Kill all running processes
ii. run the following sql -

dbcc shrinkdatabase (databasename, 'target percent')

Method 2 : Use the TSQL command dbcc shrinkfile to shrink the data & log files separately >

i. Kill all running processes
ii. run the following sql -

use tempdb
go
-- this command shrinks the primary data file
dbcc shrinkfile (datafile, 'target size in MB')
go
-- this command shrinks the primary data file
dbcc shrinkfile (datafilelog, 'target size in MB')
go

* you can specify a target size of 0 and SQL will shrink the file as much as it can (although the first operation on that db will inevitably trigger automatic growth)

In SQL 2005+, these tasks can be done from Management Studio by Right clicking the database, selecting 'Tasks' then 'Shrink' followed by 'Database' or 'Files'.

If the files dont shrink, identify and stop any running processes you can afford to and repeat.
Running processes can be viewed in Activity Monitor (Server > Management > Activity Monitor in Management Studio).

If they have stalled and are stuck or you simply want to stop them, right click the process and select 'Kill Process'. Then click 'Yes' to confirm.


Shrinking TempDB

TempDB is a SQL Server System Database. It is a temporary database, used both as a work area for internal processing and to store temporary objects created by users.
It is recreated each time SQL Server starts.

Method 1 (Downtime allowed) -

Stop and Start the SQL Server service.
TempDB will be recreated at its initial size as set in the Files page of the Database Properties.


Method 2 (No Downtime) -

Kill as many processes as you can afford to, and use the TSQL commands e.g.

use tempdb
go
dbcc shrinkfile (tempdev, 'target size in MB')
go
dbcc shrinkfile (templog, 'target size in MB')
go

( In SQL 2005+, this can be done from Management Studio by Right clicking the database, selecting 'Tasks' then 'Shrink' followed by 'Database' or 'Files'. )

Networking : Black Hole Router

Matter entering a black hole in space is never seen again.
Ditto for network packets entering a 'Black Hole Router'.

A black hole router is one that simply drops packets destined for an ip address without even reporting that the host is not reachable.

http://en.wikipedia.org/wiki/Black_hole_%28networking%29