PlItems

ID: 137
PlCodelang: T-SQL
PlGroup: Datenmengen
PlItemTitle: Differenzen anzeigen
Code: SELECT field_to_compare FROM Destination_Table EXCEPT SELECT field_to_compare FROM Source_Table

ID: 138
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: String nach einem Teilstring durchsuchen
Code: -- Find characters which are also special characters for search terms, e.g. % or _ -- see also: http://msdn.microsoft.com/en-us/library/ms179859%28v=sql.105%29.aspx SELECT * FROM table_1 WHERE column_1 LIKE '%!_%' ESCAPE '!' -- Find results which includes the character "_". The escape character is defined as "!"

ID: 139
PlCodelang: T-SQL
PlGroup: Datenbank-Administration
PlItemTitle: Alle Transaktion/Jounal-Log Files verkleinern
Code: -- The script is designed to work on a sql server -- 2008R2. However it works on 2005 and sql server -- 2012 instances. -- It shrinks all log files for the databases -- created by users -- Written by Igor Micev, 2012 -- from SQLServerCentral.com declare @logname nvarchar(128) declare @dbname nvarchar(128) declare @dynamic_command nvarchar(1024) set @dynamic_command = null declare log_cursor cursor for select db_name(mf.database_id),name from sys.master_files mf where mf.database_id not in (1,2,3,4) --avoid system databases and mf.name not like 'ReportServer$%' and right(mf.physical_name,4) = '.ldf' and mf.state_desc='online' open log_cursor fetch next from log_cursor into @dbname,@logname while @@fetch_status = 0 begin set @dynamic_command = 'USE '+@dbname+' DBCC SHRINKFILE(N'''+@logname+''',0,TRUNCATEONLY)' exec sp_executesql @dynamic_command fetch next from log_cursor into @dbname,@logname set @dynamic_command = null end close log_cursor deallocate log_cursor

ID: 140
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: Aktuellen Datenbanknamen ausgeben
Code: SELECT DB_NAME() AS DB_Name

ID: 151
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: Automatisch hochzählendes Feld
Code: CREATE TABLE myTable ( id INT IDENTIY(1,1), field1 varchar(100) )

ID: 158
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: Schema-Vergleich
Code: SELECT * FROM ( SELECT TABLES_P.TABLE_SCHEMA AS [SCHEMA_P] , TABLES_P.TABLE_NAME AS [OBJECTNAME_P] , TABLES_P.TABLE_TYPE AS [OBJECTTYPE_P] FROM <DATABASE_PRODUCTION>.INFORMATION_SCHEMA.TABLES AS TABLES_P UNION ALL SELECT ROUTINES_P.ROUTINE_SCHEMA AS [SCHEMA_P] , ROUTINES_P.ROUTINE_NAME AS [OBJECTNAME_P] , ROUTINES_P.ROUTINE_TYPE AS [OBJECTTYPE_P] FROM <DATABASE_PRODUCTION>.INFORMATION_SCHEMA.ROUTINES AS ROUTINES_P UNION ALL SELECT '' AS [SCHEMA_P] , TRIG.name AS [OBJECTNAME_P] , 'TRIGGER TABLE' AS [OBJECTTYPE_P] FROM <DATABASE_PRODUCTION>.sys.triggers AS TRIG INNER JOIN <DATABASE_PRODUCTION>.sys.tables AS TAB ON TRIG.parent_id = TAB.object_id UNION ALL SELECT '' AS [SCHEMA_P] , TRIG.name AS [OBJECTNAME_P] , 'TRIGGER VIEW' AS [OBJECTTYPE_P] FROM <DATABASE_PRODUCTION>.sys.triggers AS TRIG INNER JOIN <DATABASE_PRODUCTION>.sys.views AS TAB ON TRIG.parent_id = TAB.object_id ) ss_P FULL OUTER JOIN ( SELECT TABLES_E.TABLE_SCHEMA AS [SCHEMA_E] , TABLES_E.TABLE_NAME AS [OBJECTNAME_E] , TABLES_E.TABLE_TYPE AS [OBJECTTYPE_E] FROM <DATABASE_DEVELOPMENT>.INFORMATION_SCHEMA.TABLES AS TABLES_E UNION ALL SELECT ROUTINES_E.ROUTINE_SCHEMA AS [SCHEMA_E] , ROUTINES_E.ROUTINE_NAME AS [OBJECTNAME_E] , ROUTINES_E.ROUTINE_TYPE AS [OBJECTTYPE_E] FROM <DATABASE_DEVELOPMENT>.INFORMATION_SCHEMA.ROUTINES AS ROUTINES_E UNION ALL SELECT '' AS [SCHEMA_P] , TRIG.name AS [OBJECTNAME_P] , 'TRIGGER TABLE' AS [OBJECTTYPE_P] FROM <DATABASE_DEVELOPMENT>.sys.triggers AS TRIG INNER JOIN <DATABASE_DEVELOPMENT>.sys.tables AS TAB ON TRIG.parent_id = TAB.object_id UNION ALL SELECT '' AS [SCHEMA_P] , TRIG.name AS [OBJECTNAME_P] , 'TRIGGER VIEW' AS [OBJECTTYPE_P] FROM <DATABASE_DEVELOPMENT>.sys.triggers AS TRIG INNER JOIN <DATABASE_DEVELOPMENT>.sys.views AS TAB ON TRIG.parent_id = TAB.object_id ) ss_E ON ss_P.[SCHEMA_P]=ss_E.[SCHEMA_E] AND ss_P.OBJECTNAME_P=ss_E.OBJECTNAME_E AND ss_P.OBJECTTYPE_P=ss_E.OBJECTTYPE_E

ID: 173
PlCodelang: T-SQL
PlGroup: Datum, Zeit
PlItemTitle: Zeitdifferenz
Code: --http://msdn.microsoft.com/de-de/library/ms189794.aspx SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000') AS Seconds

ID: 180
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: NULL Wert eines Feldes übersteuern
Code: SELECT ISNULL(MyColumn1, 1.0) AS MyColumnWithoutNULL;

ID: 185
PlCodelang: T-SQL
PlGroup: Strings
PlItemTitle: Prüfen ob eine Zahl in einem String enthalten ist
Code: SELECT value ,CASE WHEN PATINDEX('%[0-9]%',value) > 0 THEN 1 ELSE 0 END AS CheckIfStringContainsANumber FROM ( SELECT 'String w/o a number' AS value UNION ALL SELECT 'This string contains 1 as a number' AS value ) subQuery1 -- value | CheckIfStringContainsANumber -- ------------------------------------------------------------------ -- String w/o a number | 0 -- This string contains 1 as a number | 1

ID: 207
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: Manuell einen AutoIncrement/Identity/Serial Wert in Tabelle einfügen
Code: SET IDENTITY_INSERT myTable ON INSERT INTO myTable(Identity_ID, Value) VALUES (4711, 'Back To The Future...') SET IDENTITY_INSERT myTable OFF

ID: 210
PlCodelang: T-SQL
PlGroup: Datum, Zeit
PlItemTitle: Kalenderwoche eines Datums
Code: select DATEPART(ISO_WEEK,CAST('2014-08-25' AS DATE))

ID: 213
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: DB-Funktion erstellen
Code: -- Table-value -- Variant 1 CREATE FUNCTION whichContinent (@country nvarchar(15)) RETURNS TABLE AS RETURN ( SELECT @country AS Country ) -- Variant 2 CREATE FUNCTION whichContinent (@country nvarchar(15)) RETURNS @returnTableResult TABLE ( id int ,currency VARCHAR(100) ) AS BEGIN IF @country='Germany' BEGIN INSERT INTO @returnTableResult VALUES (1,'EURO') END RETURN END

ID: 214
PlCodelang: T-SQL
PlGroup: Exceptions
PlItemTitle: Try Catch Except
Code: -- See also: http://msdn.microsoft.com/de-de/library/ms175976(v=sql.105).aspx -- !! -- TRY-CATCH cannot be used for all operations/situations in T-SQL. -- Be careful and refer to the link above for details -- !! BEGIN TRY SELECT 1/0; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH;

ID: 217
PlCodelang: T-SQL
PlGroup: Datenmengen
PlItemTitle: Anzahl Zeilen Resultset begrenzen
Code: SELECT TOP 100 * FROM tableA

ID: 220
PlCodelang: T-SQL
PlGroup: Strings
PlItemTitle: Position eines Teilstrings finden
Code: DECLARE @searchFor_Charindex Varchar(10) DECLARE @searchFor_Patindex Varchar(10) DECLARE @text Varchar(100) SET @searchFor_Charindex = 'simple' SET @searchFor_Patindex = '%simple%' -- Remember the %. Patindex can also be used for RegEx! SET @text = 'this is a simple text' SELECT CHARINDEX(@searchFor_Charindex, @text) AS Position_Charindex SELECT PATINDEX(@searchFor_Patindex, @text) AS Position_Patindex -- 11

ID: 226
PlCodelang: T-SQL
PlGroup: Systeminfo
PlItemTitle: Hostnamen ermitteln
Code: SELECT HOST_NAME() AS Hostname; -- prints myComputer1 or srvWindows1

ID: 241
PlCodelang: T-SQL
PlGroup: Strings
PlItemTitle: Text in Großschrift oder Kleinschrift
Code: SELECT 'my lovely Mr Singing Club' AS myString ,UPPER('my lovely Mr Singing Club') AS myStringUpper ,LOWER('my lovely Mr Singing Club') AS myStringLower -- myString: my lovely Mr Singing Club -- myStringUpper: MY LOVELY MR SINGING CLUB -- myStringLower: my lovely mr singing club

ID: 244
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: SQL SELECT Statische Werte
Code: SELECT * FROM ( VALUES (1, 2), (3, 4) ) AS q (col1, col2) GO -- the same as: SELECT 1 AS col1, 2 AS col2 UNION ALL SELECT 3 AS col1, 4 AS col2 GO -- col1 | col2 -- =========== -- 1 | 2 -- ----------- -- 3 | 4

ID: 253
PlCodelang: T-SQL
PlGroup: Metadaten
PlItemTitle: Fremdschlüssel auflisten
Link Reference: https://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server
Code: SELECT [FK_NAME] = obj.name ,[Schema_Name] = sch.name ,[Table] = tab1.name ,[Column] = col1.name ,[Referenced_Schema_Name] = sch2.name ,[Referenced_Table] = tab2.name ,[Referenced_Column] = col2.name FROM sys.foreign_key_columns fkc INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id INNER JOIN sys.schemas sch2 ON tab2.schema_id = sch.schema_id
Result Example:

FK_NAME	Schema_Name	Table	Column	Referenced_Schema_Name	Referenced_Table	Referenced_Column
CountryFK	dbo	Adress	FK_Country	dbo	Countied	ID

ID: 285
PlCodelang: Bash Script
PlGroup: Filehandling
PlItemTitle: Applikations-/Script-Verzeichnis ermitteln
Link Credits: https://stackoverflow.com/questions/59895/how-to-get-the-source-directory-of-a-bash-script-from-within-the-script-itself
Code: #!/bin/sh # The script is located in /tmp/abc/myscript.sh export AppPath=$(dirname $0) echo $0 # /tmp/abc/myscript.sh echo $AppPath # /tmp/abc
Result Example:

/tmp/abc/myscript.sh /tmp/abc