T-SQL Syntax Commands Examples

Examples Commands Syntax T-SQL
-- Tabelle Kopieren
select * into newTable from oldTable

-- Tabelle löschen
drop table TabellenName

-- Update mit RowCount und bei nichts gefunden insert
UPDATE Tabelle SET Wert = Wert 
WHERE Kriterium
IF @@RowCount = 0
BEGIN
   INSERT INTO () VALUES ()
END

-- PIVOT
SELECT v.id
        FROM Tabelle v WITH (nolock)  LEFT OUTER JOIN
          (SELECT pivott.*
           FROM (SELECT *
                 FROM Tabelle ve WITH (nolock) INNER JOIN
                      Tabelle vet WITH (nolock) ON ve.typid = vet.typid) 
                      x PIVOT (max(Wert) FOR Bezeichnung IN ([Eigenschaft])) AS pivott) x on v.[id] = x.[id] 
                 where v.IrgendeineTypId = 1
                 and x.[Eigenschaft1] = 1 

-- Abfrage von allen abhängigkeiten einer Tabelle
SELECT 
     so.name, sc.text, case (so.xtype) when 'P' then 'StoredProcedure' when 'V' then 'View' end AS typeofobject
FROM 
     sysobjects so
INNER JOIN
      syscomments sc 
ON 
     so.id = sc.id
WHERE 
     ((so.xtype = 'P' OR so.xtype = 'V')
AND 
    ( PATINDEX('%Tabelle%', CONVERT(VARCHAR(8000), sc.text)) > 0))


Comments are closed