Category Archives: SQL

Query um eine UI Tabelle darzustellen. Mit Filter pro Spalte, Sortierung pro Spalte, variablen Paging und Anzahl Zeilen ohne Filterung

DECLARE @Page INT = 1
, @PageSize INT = 50	
, @LanguageID INT = 46
, @ObjectID UNIQUEIDENTIFIER = '27461EA1-6EAC-47C5-8500-50A411C440D6'

, @Sort_Key BIT = NULL
, @Sort_Default BIT = NULL
, @Sort_Translation BIT = NULL

, @Filter_Key NVARCHAR(50) = NULL
, @Filter_Default NVARCHAR(255) = NULL
, @Filter_Translation NVARCHAR(MAX) = NULL 

SELECT 

 [KeyStore].[ID] AS [KeyStore_ID]
, [KeyStore].[Key] AS [KeyStore_Key]
, [KeyStore].[Default] AS [KeyStore_Default]
, [KeyStore].[ApplicationID] AS [KeyStore_ApplicationID]

, [Value].[ID] AS [Value_ID]
, [Value].[Value] AS [Trans]
, [Value].[LanguageID] AS [Value_LanguageID]

, [Language].[EnglishName] AS [Language_EnglishName] 
, TotalRows = COUNT(*) OVER() -- Return Total Rows. Without Offset

FROM [Translation].[KeyStore]
LEFT JOIN [Translation].[Value] [Value] ON [Value].[KeyStoreID] = [KeyStore].[ID]
INNER JOIN [Translation].[Language] [Language] ON [Language].[ID] = @LanguageID

WHERE 
([ApplicationID] = @ObjectID AND ([Value].LanguageID IS NULL OR [Value].LanguageID = @LanguageID))  	

-- Filter: Key
AND ( (@Filter_Key IS NULL OR @Filter_Key = '') OR ([KeyStore].[Key] LIKE REPLACE(@Filter_Key, '*', '%') OR (DIFFERENCE([KeyStore].[Key], @Filter_Key) > 3) ) )

-- Filter: Default
AND ( (@Filter_Default IS NULL OR @Filter_Default = '') OR ([KeyStore].[Default] LIKE REPLACE(@Filter_Default, '*', '%') OR (DIFFERENCE([KeyStore].[Default], @Filter_Default) > 3) ) )

-- Filter: Translation
AND ( (@Filter_Translation IS NULL OR @Filter_Translation = '') OR ([Value].[Value] LIKE REPLACE(@Filter_Translation, '*', '%') OR (DIFFERENCE([Value].[Value], @Filter_Translation) > 3) ) )
 
ORDER BY	
	-- Sort: Key		
	CASE @Sort_Key WHEN 0 THEN [KeyStore].[Key] END DESC, CASE @Sort_Key WHEN 1 THEN [KeyStore].[Key] END ASC
	-- Sort: Default
	, CASE @Sort_Default WHEN 0 THEN [KeyStore].[Default] END DESC, CASE @Sort_Default WHEN 1 THEN [KeyStore].[Default] END ASC
	-- Sort: Value
	, CASE @Sort_Translation WHEN 0 THEN [Value].[Value] END DESC, CASE @Sort_Translation WHEN 1 THEN [Value].[Value] END ASC
	-- Default Sort	
	, CASE WHEN @Sort_Key IS NULL AND @Sort_Default IS NULL AND @Sort_Translation IS NOT NULL THEN 	[KeyStore].[Key] END ASC
	-- Offset paging
	OFFSET ((@Page - 1) * @PageSize) ROWS FETCH NEXT @PageSize ROWS ONLY

DECLARE @Page INT = 1

, @PageSize INT = 50

, @LanguageID INT = 46

, @ObjectID UNIQUEIDENTIFIER = '27461EA1-6EAC-47C5-8500-50A411C440D6'

, @Sort_Key BIT = NULL

, @Sort_Default BIT = NULL

, @Sort_Translation BIT = NULL

, @Filter_Key NVARCHAR(50) = NULL

, @Filter_Default NVARCHAR(255) = NULL

, @Filter_Translation NVARCHAR(MAX) = NULL

SELECT

[KeyStore].[ID] AS [KeyStore_ID]

, [KeyStore].[Key] AS [KeyStore_Key]

, [KeyStore].[Default] AS [KeyStore_Default]

, [KeyStore].[ApplicationID] AS [KeyStore_ApplicationID]

, [Value].[ID] AS [Value_ID]

, [Value].[Value] AS [Trans]

, [Value].[LanguageID] AS [Value_LanguageID]

, [Language].[EnglishName] AS [Language_EnglishName]

, TotalRows = COUNT(*) OVER() -- Return Total Rows. Without Offset

FROM [Translation].[KeyStore]

LEFT JOIN [Translation].[Value] [Value] ON [Value].[KeyStoreID] = [KeyStore].[ID]

INNER JOIN [Translation].[Language] [Language] ON [Language].[ID] = @LanguageID

WHERE

([ApplicationID] = @ObjectID AND ([Value].LanguageID IS NULL OR [Value].LanguageID = @LanguageID))

-- Filter: Key

AND ( (@Filter_Key IS NULL OR @Filter_Key = '') OR ([KeyStore].[Key] LIKE REPLACE(@Filter_Key, '*', '%') OR (DIFFERENCE([KeyStore].[Key], @Filter_Key) > 3) ) )

-- Filter: Default

AND ( (@Filter_Default IS NULL OR @Filter_Default = '') OR ([KeyStore].[Default] LIKE REPLACE(@Filter_Default, '*', '%') OR (DIFFERENCE([KeyStore].[Default], @Filter_Default) > 3) ) )

-- Filter: Translation

AND ( (@Filter_Translation IS NULL OR @Filter_Translation = '') OR ([Value].[Value] LIKE REPLACE(@Filter_Translation, '*', '%') OR (DIFFERENCE([Value].[Value], @Filter_Translation) > 3) ) )

ORDER BY

-- Sort: Key

CASE @Sort_Key WHEN 0 THEN [KeyStore].[Key] END DESC, CASE @Sort_Key WHEN 1 THEN [KeyStore].[Key] END ASC

-- Sort: Default

, CASE @Sort_Default WHEN 0 THEN [KeyStore].[Default] END DESC, CASE @Sort_Default WHEN 1 THEN [KeyStore].[Default] END ASC

-- Sort: Value

, CASE @Sort_Translation WHEN 0 THEN [Value].[Value] END DESC, CASE @Sort_Translation WHEN 1 THEN [Value].[Value] END ASC

-- Default Sort

, CASE WHEN @Sort_Key IS NULL AND @Sort_Default IS NULL AND @Sort_Translation IS NOT NULL THEN [KeyStore].[Key] END ASC

-- Offset paging

OFFSET ((@Page - 1) * @PageSize) ROWS FETCH NEXT @PageSize ROWS ONLY

Posted on 5. November 2018 by Andreas in SQL

Spalteninformationen zu Prozeduren anzeigen

Der Profiler ist schon eine tolle Sache. Wenn man im SQL Management Studio eine Prozedur ausklappt, dann macht das Management Studio folgende Abfrage um die Informationen auszulesen:

SELECT
param.name AS [Name],
'Server[@Name=' + quotename(CAST(
        serverproperty(N'Servername')
       AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/StoredProcedure[@Name=' + quotename(sp.name,'''') + ' and @Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''') + ']' + '/Param[@Name=' + quotename(param.name,'''') + ']' AS [Urn],
usrt.name AS [DataType],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length],
CAST(param.precision AS int) AS [NumericPrecision],
CAST(param.scale AS int) AS [NumericScale],
param.default_value AS [DefaultValue],
param.is_output AS [IsOutputParameter],
param.is_cursor_ref AS [IsCursorParameter],
sp.object_id AS [IDText],
db_name() AS [DatabaseName],
param.name AS [ParamName],
CAST(
 case 
    when sp.is_ms_shipped = 1 then 1
    when (
        select 
            major_id 
        from 
            sys.extended_properties 
        where 
            major_id = sp.object_id and 
            minor_id = 0 and 
            class = 1 and 
            name = N'microsoft_database_tools_support') 
        is not null then 1
    else 0
end          
             AS bit) AS [ParentSysObj],
1 AS [Number]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = param.system_type_id) and (baset.user_type_id = param.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))

SELECT

param.name AS [Name],

'Server[@Name=' + quotename(CAST(

serverproperty(N'Servername')

AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/StoredProcedure[@Name=' + quotename(sp.name,'''') + ' and @Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''') + ']' + '/Param[@Name=' + quotename(param.name,'''') + ']' AS [Urn],

usrt.name AS [DataType],

ISNULL(baset.name, N'') AS [SystemType],

CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length],

CAST(param.precision AS int) AS [NumericPrecision],

CAST(param.scale AS int) AS [NumericScale],

param.default_value AS [DefaultValue],

param.is_output AS [IsOutputParameter],

param.is_cursor_ref AS [IsCursorParameter],

sp.object_id AS [IDText],

db_name() AS [DatabaseName],

param.name AS [ParamName],

CAST(

case

when sp.is_ms_shipped = 1 then 1

when (

select

major_id

from

sys.extended_properties

where

major_id = sp.object_id and

minor_id = 0 and

class = 1 and

name = N'microsoft_database_tools_support')

is not null then 1

else 0

end

AS bit) AS [ParentSysObj],

1 AS [Number]

FROM

sys.all_objects AS sp

INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id

LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id

LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = param.system_type_id) and (baset.user_type_id = param.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))

Posted on 4. November 2018 by Andreas in SQL

Alle Country Codes als SQL Query

Einfach eine Konsolenanwendung erstellen und folgendes Snippet reinkopieren:

Posted on 4. September 2018 by Andreas in C#, SQL

SQL Backup erzeugen

-- database name  
DECLARE @name VARCHAR(50) = 'MyDatabase';
-- specify filedate format (yyyy-mm-dd)
DECLARE @fileDate VARCHAR(20) = REPLACE(CONVERT(VARCHAR(20),GETDATE(),111),'/','-') 
-- path for backup files  (Muss mit \ enden)
DECLARE @path VARCHAR(256) = 'MyBackupDirectory\'  
DECLARE @fileName VARCHAR(256) = CONCAT(@path, @name, '_', @fileDate, '.bak');


BACKUP DATABASE @name TO DISK = @fileName

-- database name

DECLARE @name VARCHAR(50) = 'MyDatabase';

-- specify filedate format (yyyy-mm-dd)

DECLARE @fileDate VARCHAR(20) = REPLACE(CONVERT(VARCHAR(20),GETDATE(),111),'/','-')

-- path for backup files (Muss mit \ enden)

DECLARE @path VARCHAR(256) = 'MyBackupDirectory\'

DECLARE @fileName VARCHAR(256) = CONCAT(@path, @name, '_', @fileDate, '.bak');

BACKUP DATABASE @name TO DISK = @fileName

Kurz und knackiger Skript um ein Backup zu erzeugen. Ergebnis: MyDatabase_2018-04-30.bak Andrere Datumsformate auch hier: https://www.techonthenet.com/sql_server/functions/convert.php

Posted on 30. April 2018 by Andreas in SQL

Insert and retrieve autogenerated ID

Möchte man einen Insert durchführen und daraufhin die generierte ID erhalten, so geht es beim Identifiert (Int) sehr einfach über den OUTPUT Parameter:

CREATE PROCEDURE dbo.MyTable_Insert
	 @ID Int OUTPUT
	, @ColumnA nvarchar(10)
	, @ColumnB nvarchar(10)

INSERT INTO dbo.MyTable (
					ColumnA
					, ColumnB
				)
				VALUES (
					@ColumnA
					, @ColumnB
				)
			
			SELECT @ID = @@IDENTITY

CREATE PROCEDURE dbo.MyTable_Insert

@ID Int OUTPUT

, @ColumnA nvarchar(10)

, @ColumnB nvarchar(10)

INSERT INTO dbo.MyTable (

ColumnA

, ColumnB

)

VALUES (

@ColumnA

, @ColumnB

)

SELECT @ID = @@IDENTITY

Beim Uniqueidentifier (GUID) ist das Selektieren eines @@Identity nicht möglich. Daher muss eine temporäre Tabelle hinhalten:

CREATE PROCEDURE dbo.MyTable_Insert
	 @ID Uniqueidentifier OUTPUT -- Ebenfalls den OUTPUT Parameter
	, @ColumnA nvarchar(10)
	, @ColumnB nvarchar(10)

CREATE TABLE #tmp(ID uniqueidentifier)	-- Temporäre Tabelle erzeugen
	
INSERT INTO dbo.MyTable (
					ColumnA
					, ColumnB
				)
				OUTPUT inserted.ID INTO #tmp -- Die ID in die temp. Tabelle schreiben
				VALUES (
					@ColumnA
					, @ColumnB
				)
			
			SELECT @ID = (SELECT TOP 1 ID FROM #tmp) -- Die ID wieder selektieren

CREATE PROCEDURE dbo.MyTable_Insert

@ID Uniqueidentifier OUTPUT -- Ebenfalls den OUTPUT Parameter

, @ColumnA nvarchar(10)

, @ColumnB nvarchar(10)

CREATE TABLE #tmp(ID uniqueidentifier) -- Temporäre Tabelle erzeugen

INSERT INTO dbo.MyTable (

ColumnA

, ColumnB

)

OUTPUT inserted.ID INTO #tmp -- Die ID in die temp. Tabelle schreiben

VALUES (

@ColumnA

, @ColumnB

)

SELECT @ID = (SELECT TOP 1 ID FROM #tmp) -- Die ID wieder selektieren

Posted on 12. April 2018 by Andreas in SQL

SQL Funktion um gleich klingende Wörter zu erkennen

SQL hat eine interne Funktion, um Wörter, die gleich klingen nach einer mathematischen Formel zu berechnen. Beispiel:

SELECT SOUNDEX('Zicke')
SELECT SOUNDEX('Ziege')

1 2	SELECT SOUNDEX('Zicke') SELECT SOUNDEX('Ziege')

bekommen beide den Wert B162. Diese Funktion ist besonders für Suchanfragen perfekt.

Posted on 11. Oktober 2017 by Andreas in Allgemein, SQL

Tags: ähnlich, Formel, gleich klingend, Phonographie, Soundex, suche

Trigger, der tatsächlich alles löscht

Wenn man in T-SQL einen Trigger auf eine Tabelle legt und diese soll nach einem Löschvorgang ebenfalls etwas anderes machen, dann stoßt man auf das folgende Problem. Solange man nur eine Zeile löscht, ist alles in Ordnung, sobald man aber mehrere Datensätze löscht, kann es passieren, dass nur der erste Trigger greift. Der Grund ist, […]

Posted on 8. Oktober 2017 by Andreas in Allgemein, SQL

Tags: Cursor, Delete, deleted, Fetch, Trigger

SQL Management Studio Sitzung neu laden

Wenn man z.B. eine neue Spalte angelegt hat und möchte dann, diese im Query Editor ansprechen, so wird diese rot angestrichen. Das bedeutet, dass er in dieser Sitzung diese Spalte noch nicht kennt. Mit der Tastenkombination Strg + Shift + R kann man die Sitzung aber einfach neu starten.

Posted on 19. September 2017 by Andreas in SQL

Ganze Datenbank nach einem Begriff durchsuchen

DECLARE @SearchStr nvarchar(100) = 'Untergeordnet'

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

	SET NOCOUNT ON

	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
	
			IF @ColumnName IS NOT NULL
			BEGIN
				INSERT INTO #Results
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			END
		END	
	END

	SELECT ColumnName, ColumnValue FROM #Results

DECLARE @SearchStr nvarchar(100) = 'Untergeordnet'

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ''

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN

SET @ColumnName = ''

SET @TableName =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

AND OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

), 'IsMSShipped'

) = 0

)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN

SET @ColumnName =

(

SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)

AND TABLE_NAME = PARSENAME(@TableName, 1)

AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

AND QUOTENAME(COLUMN_NAME) > @ColumnName

)

IF @ColumnName IS NOT NULL

BEGIN

INSERT INTO #Results

EXEC

(

'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)

FROM ' + @TableName + ' (NOLOCK) ' +

' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

)

END

SELECT ColumnName, ColumnValue FROM #Results

ACHTUNG: Das Script ist nicht von mir. http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Posted on 8. August 2017 by Andreas in SQL

SQL Datenbank von .bak Wiederherstellen

use master
ALTER DATABASE [DATABASENAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

RESTORE FILELISTONLY
   FROM disk = 'C:\temp\BACKUPFILE.bak'    
-- Restore the files for 
RESTORE DATABASE [DATABASENAME]
   FROM disk = 'C:\temp\BACKUPFILE.bak'
   WITH replace,
   MOVE 'MDF_FILENAME' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MDF_FILENAME.mdf', 
   MOVE 'LDF_FILENAME' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\LDF_FILENAME.ldf', 
   stats = 5
GO

ALTER DATABASE [DATABASENAME] SET MULTI_USER;
GO

use master

ALTER DATABASE [DATABASENAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE FILELISTONLY

FROM disk = 'C:\temp\BACKUPFILE.bak'

-- Restore the files for

RESTORE DATABASE [DATABASENAME]

FROM disk = 'C:\temp\BACKUPFILE.bak'

WITH replace,

MOVE 'MDF_FILENAME' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MDF_FILENAME.mdf',

MOVE 'LDF_FILENAME' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\LDF_FILENAME.ldf',

stats = 5

ALTER DATABASE [DATABASENAME] SET MULTI_USER;

Ersetze DATABASENAME durch den Datenbanknamen, der wiederhergestellt werden soll (Achtung! 2x) BACKUPFILE.bak durch den Pfad zur Backup Datei MDF_FILENAME durch die Bezeichnung der Mdf Datei LDF_FILENAME durch die Bezeichnung der LDF Datei. In der Regel heißt sie wie die Mdf Datei mit „_log“ dahinter

Posted on 24. Januar 2017 by Andreas in SQL

Tags: .bak, Backup, Restore, SQL, Wiederherstellen

Denn ich habe kein Gefallen am Tod dessen, der sterben muss, spricht Gott, der Herr. So kehrt denn um, und ihr sollt leben!

Category Archives: SQL

Query um eine UI Tabelle darzustellen. Mit Filter pro Spalte, Sortierung pro Spalte, variablen Paging und Anzahl Zeilen ohne Filterung

Spalteninformationen zu Prozeduren anzeigen

Alle Country Codes als SQL Query

SQL Backup erzeugen

Insert and retrieve autogenerated ID

SQL Funktion um gleich klingende Wörter zu erkennen

Trigger, der tatsächlich alles löscht

SQL Management Studio Sitzung neu laden

Ganze Datenbank nach einem Begriff durchsuchen

SQL Datenbank von .bak Wiederherstellen

Seiten

Neueste Beiträge

Neueste Kommentare

Archive

Denn ich habe kein Gefallen am Tod dessen, der sterben muss, spricht Gott, der Herr. So kehrt denn um, und ihr sollt leben!

Category Archives: SQL

Search this site

Seiten

Neueste Beiträge

Neueste Kommentare

Archive