You may optionally enter a comparison operator (<, <=, >, >=, <> or =) at the beginning of each of your search values to specify how the comparison should be done.
SELECT field_to_compare FROM Destination_Table
EXCEPT
SELECT field_to_compare FROM Source_Table
138
T-SQL
Datenbank
String nach einem Teilstring durchsuchen
-- 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 "!"
139
T-SQL
Datenbank-Administration
Alle Transaktion/Jounal-Log Files verkleinern
-- 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
140
T-SQL
Datenbank
Aktuellen Datenbanknamen ausgeben
SELECT DB_NAME() AS DB_Name
151
T-SQL
Datenbank
Automatisch hochzählendes Feld
CREATE TABLE myTable
(
id INT IDENTIY(1,1),
field1 varchar(100)
)
158
T-SQL
Datenbank
Schema-Vergleich
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
173
T-SQL
Datum, Zeit
Zeitdifferenz
--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
180
T-SQL
Datenbank
NULL Wert eines Feldes übersteuern
SELECT ISNULL(MyColumn1, 1.0) AS MyColumnWithoutNULL;
185
T-SQL
Strings
Prüfen ob eine Zahl in einem String enthalten ist
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
207
T-SQL
Datenbank
Manuell einen AutoIncrement/Identity/Serial Wert in Tabelle einfügen
SET IDENTITY_INSERT myTable ON
INSERT INTO myTable(Identity_ID, Value) VALUES (4711, 'Back To The Future...')
SET IDENTITY_INSERT myTable OFF
