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 GETDATE();
INSERT INTO table (datetime) VALUES (GETDATE());
57
T-SQL
Datenbank
SQL-Cursor
CREATE #myTempTable
(
ID Int IDENTITY
,tempField1 Int
,tempField2 VarChar(30)
)
DECLARE @field1 AS Int
DECLARE @field2 AS VarChar(30)
DECLARE myCursor CURSOR FOR SELECT myTableField1, myTableField2 FROM myTable
OPEN myCursor
FETCH NEXT FROM myCursor INTO @field1, @field2;
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @field1 -- Prints value from column myTable.myTableField1
PRINT @field2 -- Prints value from column myTable.myTableField2
INSERT INTO #myTempTable (tempField1,tempField2) VALUES (@field1, @field2)
-- do something else...
FETCH NEXT FROM myCursor INTO @field1, @field2; -- next row
END
CLOSE myCursor
DEALLOCATE myCursor
DROP #myTempTable
59
T-SQL
Datenbank
Fortlaufende Nummer innerhalb eines ResultSets
-- Beispiel:
-- http://msdn.microsoft.com/de-de/library/ms186734.aspx
SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode FROM Sales.vSalesPerson
60
T-SQL
Datenbank
Nach Inhalt in Functions/Stored Procedures suchen
-- Durchsucht alle Functions, Stored Procedures, Views nach dem enthaltenen Begriff dbo.table1:
DECLARE @suchbegriff VarChar(50) = '%dbo.table1%'
SELECT
*
FROM
(
SELECT
ROUTINE_SCHEMA AS [Schema]
, ROUTINE_NAME AS [Objectname]
, ROUTINE_DEFINITION AS [Objectdefinition]
, ROUTINE_TYPE AS [Objecttype]
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE @suchbegriff
AND (ROUTINE_TYPE='PROCEDURE' OR ROUTINE_TYPE='FUNCTION')
UNION ALL
SELECT
TABLE_SCHEMA AS [Schema]
, TABLE_NAME AS [Objectname]
, VIEW_DEFINITION AS [Objectdefinition]
, 'VIEW' AS [Objecttype]
FROM
INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE @suchbegriff
) subQuery1
ORDER BY subQuery1.[Objectname]
-- gefunden bei: http://www.mssqltips.com/tip.asp?tip=1419
-- erweitert um View-Definition
63
T-SQL
Datenbank
Tabellenstruktur kopieren
Select *
INTO SQL2.dbo.NeueTabelle
FROM SQL1.dbo.AlteTabelle
WHERE 0=1
64
T-SQL
Datenbank
Prüfen ob temporäre Tabelle existiert
SELECT OBJECT_ID ('tempdb..#myTableTable1')
65
T-SQL
Datenbank
Prüfen welche/ob Tabelle ein AutoIncrement/Identiy Spalte hat
-- Welche Tabellen ...
select o.name, c.name,
from sys.objects o inner join sys.columns c on o.object_id = c.object_id
where c.is_identity = 1
-- --------------------------------------
-- Hat diese Tabelle ...
IF ((SELECT OBJECTPROPERTY( OBJECT_ID(N'dbo.myTable'), 'TableHasIdentity')) = 1)
PRINT 'Yes'
ELSE
PRINT 'No'
