Category Archives: Database

T-SQL: Smazání všech tabulek a dalších běžných objektů z DB

Zdroj: http://stackoverflow.com/questions/536350/drop-all-the-tables-stored-procedures-triggers-constraints-and-all-the-depend

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

Instalace SQL Server 2016 (Express) LocalDB

SQL Server 2016 RTM, na rozdíl třeba od své RC0, již ve svém hlavním instalátoru nenabízí check-box na instalaci LocalDB, přesněji řečeno “někdy nabízí, někdy nenabízí”. Zřejmě podle toho, jestli už na stroji něco nachází, nebo ne. Každopádně doinstalaci provedete samostatným instalátorem, který je na SQL Server DVD:

<dvd>:\1033_ENU_LP\x64\Setup\x64\SqlLocalDB.msi

PS: Při instalaci SQL Serveru 2016 Express by měl být checkbox LocalDB ve fázi Feature Selection, ale ani na Expressu se mi nenabízel.

SQL Worst Practices & Optimalizace – slides a dema [WUG Hradec Králové 11/2015]

Slides a dema z mé přednášky pro Windows User Group Hradec Králové z 12.11.2015.

Záznam je publikován na našem HAVIT YouTube Channelu.

Dotčená témata:

  • Pyramida zacílení optimalizačních snah
  • Connection Pooling
  • SQL Injection
  • DB Schema
  • Import vs. hromadné Enable/Disable Constraints
  • [Non-]Clustered Index, Heap
  • Execution Plans
    • Query Cost, Statistiky
    • Operace v execution planu
  • Set-based vs. Row-based logika
  • Různé zápisy stejného dotazu
  • Cachování execution planu
  • Práce s časem a datam vs. rychlost

Microsoft SQL Server – kill locku na databázi

Pokud chceme odblokovat lock-nutou databázi na Microsoft SQL Serveru (například pokud chceme přejmenovat databázi, provést obnovu databáze, výmaz databáze…), lze to udělat následujícími způsoby.

První možností je použít tento kód:

SELECT DISTINCT
  name AS database_name,
  session_id,
  host_name,
  login_time,
  login_name,
  reads,
  writes
FROM sys.dm_exec_sessions
    LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
    INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
  WHERE name ='NazevMeDatabaze'
  ORDER BY name

Následně se nám objeví, kdo má nad databází zámek:

SQL_select_lock

Pak lze použít například „KILL 68„, čímž zámek uvolníme. Výhodou je, že vidíme, kdo má lock a lze vše ručně řídit. Nevýhodou je, že mohou přibýt další zámky před operací, kterou chceme vykonat.

Další možností je použít tento kód:

ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--do you stuff here
ALTER DATABASE YourDatabase SET MULTI_USER

Zde je určitou nevýhodou to, že nám zůstanou na databázi naše vlastní zámky a také to, že můžeme zapomenout vrátit databázi do multi-user režimu. Akce provádíme v místě označeném „do you stuff here“.

sp_MSForeachDB – Vykonání SQL příkazů pro každou DB

Microsoft SQL Server má nedokumentovanou uloženou proceduru, která usnadní vykonání stejného SQL příkazu pro více DB. Např. nalezení CLR assemblies přes všechny DB:

sp_MSForeachDB 'use [?]; SELECT DB_NAME(), * FROM sys.assemblies WHERE name LIKE ''Havit%'';'

Pokud chceme příkaz vykonat s výjimkou některých DB, můžeme použít IF:

sp_MSForeach 'IF ? NOT IN (''master'', ''msdb'', ''tempdb'', ''model'') USE [?]; -- atp'

(Pozor, že pokud dáte pod IF jenom USE, tak se Vám následný příkaz vykoná pro aktuální DB několikrát, protože neproběhne jenom přepnutí USE na jinou DB.)

SQL Server Performance – Execution Plans – záznam, slides a dema [TechEd Praha 2014]

Dema a slides z mé přednášky pro Gopas TechEd DevCon Praha 2014 – inovovaná podoba přednášky, kterou jsem již dělal pro MS Fest (skoro stejné) nebo WUG Praha (asi nejlepší plná verze bez časové tísně, ale zas má něco přes tři hodiny):

Z přednášky jsem pořizoval záznam, který najdete na našem HAVIT YouTube Channelu, tato podoba má ze tří možných asi nejkvalitnější záznam (720p screenrec):