klaushaidinger.com

Icon

Recursive queries in SqlServer

WITH MeineGruppen(Parent, Subitem) as
(
— Base query
SELECT n_parent, n_lfdgruppe
FROM ugruppe
WHERE n_parent = 10625
UNION ALL
— Recursive query
SELECT me.Parent, dm.n_lfdgruppe
FROM MeineGruppen AS me
JOIN ugruppe AS dm
ON me.Subitem = dm.n_parent
WHERE me.Parent = 10625
)
SELECT * FROM MeineGruppen ORDER BY Subitem

Finding the biggest tables in a database

One nice feature about SQL Server is the source code for system stored procedures, is readily available. sp_spaceused, which is a nifty little (master) sp that will tell you how big your datbase is, can be opened and viewed just like any other sp. I did some changes and voilá: run this small script to find out the biggest tables in your database.

Download Script: bigtables

Restore a SQL Server suspect Database

Execute the following script. Use at your own risk!

USE master;
EXEC sp_resetstatus ‚yourdatabase‘;
ALTER DATABASE yourdatabase SET EMERGENCY
DBCC checkdb(‚yourdatabase‘)
ALTER DATABASE yourdatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‚yourdatabase‘, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourdatabase SET MULTI_USER