Month: June 2011

Posted on by — Leave a comment

Using ROW_NUMBER()

From SQL Server 2005 Books Online:

The following example returns rows with numbers 50 to 60 inclusive in the order of the OrderDate.

USE AdventureWorks;
GO

WITH OrderedOrders AS
(
SELECT SalesOrderID
, OrderDate
, ROW_NUMBER() OVER (ORDER BY OrderDate) AS ‘RowNumber’
FROM Sales.SalesOrderHeader
)

SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;

Posted on by — Leave a comment

Default Database

/***********************************************
* This gives users a dummy default database
* in order to prevent them from:
* 1) having ‘master’ as a default database
* 2) not being able to connect to the server
* because their default database has been
* dropped, recreated, or restored
***********************************************/

USE master
GO

IF NOT EXISTS ( SELECT TOP 1 1 FROM sysdatabases WHERE name = ‘uno’ )
BEGIN
CREATE DATABASE [uno]
END
GO

ALTER DATABASE [uno] SET READ_WRITE
GO

USE uno
GO

GRANT SELECT TO [public]
GO

DECLARE @name sysname
, @sql nvarchar(max)

DECLARE login_cur CURSOR FAST_FORWARD FOR
SELECT name
FROM master..syslogins WITH (NOLOCK)
WHERE upper(name) LIKE ‘%’

OPEN login_cur

FETCH login_cur INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘IF NOT EXISTS ( SELECT TOP 1 1 FROM sysusers WHERE name = ”’ + @name + ”’ ) ‘
SET @sql = @sql + ‘CREATE USER [‘ + @name + ‘] FOR LOGIN [‘ + @name + ‘] WITH DEFAULT_SCHEMA=[dbo];’
EXEC sp_executesql @sql
SET @sql = ‘ALTER LOGIN [‘ + @name + ‘] WITH DEFAULT_DATABASE=[uno];’
EXEC sp_executesql @sql
FETCH login_cur INTO @name
END

CLOSE login_cur
DEALLOCATE login_cur
GO

–ALTER DATABASE [uno] SET READ_ONLY
–GO

Posted on by — Leave a comment

Drop all constraints (PKs & FKs) and Indexes

DECLARE @objs TABLE ( id int identity(1,1), cmd nvarchar(4000), srt tinyint )

DECLARE @id int

      , @cmd nvarchar(4000)


   INSERT INTO @objs (cmd,srt)

        SELECT 'DROP INDEX [' + A.name + '] ON [' +  + SCHEMA_NAME(B.schema_id) + + '].[' + B.name + ']'

             , 1

          FROM sys.indexes  A

    INNER JOIN sys.objects  B

            ON A.object_id = B.object_id

         WHERE A.is_primary_key = 0

           AND A.is_unique_constraint = 0

           AND B.type = 'U'

           AND A.name IS NOT NULL

   UNION

        SELECT 'ALTER TABLE [' +  + SCHEMA_NAME(B.schema_id) + + '].[' + B.name + '] DROP CONSTRAINT [' + A.name + ']'

             , 2

          FROM sys.objects  A

    INNER JOIN sys.objects  B

            ON A.parent_object_id = B.object_id

         WHERE A.type = 'PK'

            OR A.name IN ( 'PK_deemed_raiv','PK_driverchecklog')

      ORDER BY 2


SELECT @id = max(id) FROM @objs


WHILE @id > 0

 BEGIN

   SELECT @cmd = cmd FROM @objs WHERE id = @id

   EXEC sp_executesql @cmd

   SET @id = @id - 1

 END

GO

Posted on by — Leave a comment

Dropping Erroneous Tables from Master database

USE master
GO

DECLARE @tabname sysname
, @sql nvarchar(256)
DECLARE tab_cur CURSOR FAST_FORWARD FOR
SELECT name
FROM sysobjects WITH (NOLOCK)
WHERE type = ‘U’
AND ( name LIKE ‘L_%’ OR name LIKE ‘T_%’ )

OPEN tab_cur
FETCH tab_cur INTO @tabname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘DROP TABLE ‘ + @tabname
EXEC sp_executesql @sql
FETCH tab_cur INTO @tabname
END

CLOSE tab_cur
DEALLOCATE tab_cur
GO

Posted on by — Leave a comment

Dropping All Tables, SPs, FNs, and Views

USE {DATABASE_NAME}
GO

——————————————————–
— Drop Procedures
——————————————————–
DECLARE @objname sysname
DECLARE @sql nvarchar(max)

DECLARE obj_cur CURSOR FAST_FORWARD
FOR
SELECT name FROM sysobjects WHERE type = ‘P’

OPEN obj_cur

FETCH obj_cur INTO @objname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘DROP PROCEDURE ‘ + @objname
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname
END

CLOSE obj_cur
DEALLOCATE obj_cur

GO

——————————————————–
— Drop Functions
——————————————————–
DECLARE @objname sysname
DECLARE @sql nvarchar(max)
DECLARE obj_cur CURSOR FAST_FORWARD
FOR
SELECT name FROM sysobjects WHERE type IN (‘FN’,’IF’,’TF’)

OPEN obj_cur

FETCH obj_cur INTO @objname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘DROP FUNCTION ‘ + @objname
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname
END

CLOSE obj_cur
DEALLOCATE obj_cur

GO

——————————————————–
— Drop Views
——————————————————–
DECLARE @objname sysname
DECLARE @sql nvarchar(max)

DECLARE obj_cur CURSOR FAST_FORWARD
FOR
SELECT name FROM sysobjects WHERE type = ‘V’

OPEN obj_cur

FETCH obj_cur INTO @objname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘DROP VIEW ‘ + @objname
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname
END

CLOSE obj_cur
DEALLOCATE obj_cur

GO

——————————————————–
— Drop Constraints
——————————————————–
DECLARE @objname sysname
DECLARE @sql nvarchar(max)
DECLARE @tabname sysname

DECLARE obj_cur CURSOR FAST_FORWARD
FOR
SELECT A.name [const_name]
, B.name [table_name]
FROM sysobjects A
, sysobjects B
, sysconstraints C
WHERE A.id = C.constid
AND B.id = C.id
AND B.name NOT LIKE ‘sys%’
AND A.type IN ( ‘F’, ‘K’ )
ORDER BY A.type, 2 DESC, 1

OPEN obj_cur
FETCH obj_cur INTO @objname, @tabname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘ALTER TABLE ‘ + @tabname + ‘ NOCHECK CONSTRAINT ‘ + @objname
PRINT @sql
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname, @tabname
END

CLOSE obj_cur

OPEN obj_cur
FETCH obj_cur INTO @objname, @tabname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘ALTER TABLE ‘ + @tabname + ‘ DROP CONSTRAINT ‘ + @objname
PRINT @sql
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname, @tabname
END

CLOSE obj_cur
DEALLOCATE obj_cur
GO

——————————————————–
— Drop Tables
——————————————————–
DECLARE @objname sysname
DECLARE @sql nvarchar(max)
DECLARE obj_cur CURSOR FAST_FORWARD
FOR
SELECT name FROM sysobjects WHERE type = ‘U’

OPEN obj_cur

FETCH obj_cur INTO @objname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘DROP TABLE ‘ + @objname
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname
END

CLOSE obj_cur
DEALLOCATE obj_cur
GO

Posted on by — Leave a comment

Rebuilding all Indexes

USE [DATABASE_NAME]
GO

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT table_name
FROM information_schema.tables WITH (NOLOCK)
WHERE table_type = ‘base table’

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,’ ‘,90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor
GO

Posted on by — Leave a comment

Counts from Each Table in DB

USE [dbname]
GO

SET NOCOUNT ON
DECLARE @tabname sysname
DECLARE @sql nvarchar(max)
DECLARE @rows int
DECLARE table_cur CURSOR FAST_FORWARD FOR

SELECT name
FROM sysobjects
WHERE type = ‘U’

CREATE TABLE #results_tmp ( tabname sysname, cnt int )

OPEN table_cur
FETCH table_cur INTO @tabname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘INSERT INTO #results_tmp SELECT ”’ + @tabname + ”’, count(1) FROM ‘ + @tabname
EXEC sp_executesql @sql
FETCH table_cur INTO @tabname
END

CLOSE table_cur
DEALLOCATE table_cur

SELECT cnt, tabname FROM #results_tmp ORDER BY cnt DESC
DROP TABLE #results_tmp
GO

Posted on by — Leave a comment

Reordering Subgroup

USE [db]
GO

–delcare variables
DECLARE @i int
DECLARE @lastrow int
DECLARE @curPoc int
DECLARE @curDir int
DECLARE @lastDir int
DECLARE @order int

DECLARE @pocs AS TABLE
( rownum int IDENTITY(1, 1) PRIMARY KEY NOT NULL
, pocID int NOT NULL
, fk_directorateID int NOT NULL
)

— this builds a var table that is pre-sorted according to criteria
INSERT INTO @pocs ( pocID, fk_directorateID )
SELECT A.pocId
, A.fk_directorateID
FROM t_poc A (NOLOCK)
WHERE A.isArchived = 0
ORDER BY A.fk_directorateID
, A.orderBy
, A.pocID

SELECT @lastrow = max(A.rownum)
FROM @pocs A

SET @i = 1
SET @lastDir = 0
SET @order = 0

–loop through all POCs in POC table
WHILE @i

Posted on by — Leave a comment

Default data file location

To prevent the C: drive from filling up, it is a good idea to set the database default location. To do this:

1. Open SQL Server Management Studio
2. Right click the server instance
3. Select “Properties”
4. In the Server Properties window, select “Database Settings”
5. Under “Database default locations”, specify path for “Data:” and “Log:”, for example: “D:SQLDATA”

Additionally, if space on the C: drive is limited, check the properties of the TEMPDB.

This can be found under the “Databases” –> “System Databases” branches in the server’s tree-view.

First, since the tempdb does not autoshrink, you can manually shrink it by right-clicking tempdb and selecting “Task”–>”Shrink”–>”Database”.

Next, right-click the tempdb database and select “Properties”. Then select “Files”. You can set the “tempdev.mdf” file to be restricted growth and add an additional database file that is unrestricted on another drive.

Posted on by — Leave a comment

Recover from a corrupted Master DB

To rebuild a SQL 2005 master database:

start /wait setup.exe /qn
INSTANCENAME= REINSTALL=SQL_Engine
REBUILDDATABASE=1 SAPWD=

In SQL 2008 use:

setup.exe
/QUIET
/ACTION=REBUILDDATABASE
/INSTANCENAME=instance_name
/SQLSYSADMINACCOUNTS= accounts
[/SAPWD=password]
[/SQLCOLLATION=collation_name]

Then start in single user mode:

sqlservr.exe -m -s

Then restore the latest backup of master and user databases, bringing the instance to the latest point possible.

More Info:

http://www.sqlservercentral.com/articles/Administration/73207/

http://www.networkworld.com/community/node/39292