PlItems

ID: 63
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: Tabellenstruktur kopieren
Code: Select * INTO SQL2.dbo.NeueTabelle FROM SQL1.dbo.AlteTabelle WHERE 0=1

ID: 64
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: Prüfen ob temporäre Tabelle existiert
Code: SELECT OBJECT_ID ('tempdb..#myTableTable1')

ID: 65
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: Prüfen welche/ob Tabelle ein AutoIncrement/Identiy Spalte hat
Code: -- 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'

ID: 66
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: Ausführen von SQL-Code per Aufruf
Code: http://www.databasejournal.com/features/mssql/article.php/3286501/T-SQL-Programming-Part-4---Setting-Variables-in-Calling-T-SQL-Code-While-Using-spexecutesql.htm use Northwind go declare @RECCNT int declare @ORDID varchar(10) declare @CMD Nvarchar(100) set @ORDID = 10436 SET @CMD = 'SELECT @RECORDCNT=count(*) from [Orders]' + ' where OrderId < @ORDERID' print @CMD exec sp_executesql @CMD, N'@RECORDCNT int out, @ORDERID int', @RECCNT out, @ORDID print 'The number of records that have an OrderId' + ' greater than ' + @ORDID + ' is ' + cast(@RECCNT as char(5))

ID: 67
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: Tabellenzeile mit FK's löschen
Code: -- Siehe auch: http://www.sqlteam.com/article/performing-a-cascade-delete-in-sql-server-7 CREATE Procedure spDeleteRows -- Recursive row delete procedure. -- It deletes all rows in the table specified that conform to the criteria selected, -- while also deleting any child/grandchild records and so on. This is designed to do the -- same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys -- table to find any child tables, then deletes the soon-to-be orphan records from them using -- recursive calls to this procedure. Once all child records are gone, the rows are deleted -- from the selected table. It is designed at this time to be run at the command line. It could -- also be used in code, but the printed output will not be available. ( @cTableName varchar(50), -- name of the table where rows are to be deleted @cCriteria nvarchar(1000), -- criteria used to delete the rows required @iRowsAffected int OUTPUT -- number of records affected by the delete ) As set nocount on declare @cTab varchar(255), -- name of the child table @cCol varchar(255), -- name of the linking field on the child table @cRefTab varchar(255), -- name of the parent table @cRefCol varchar(255), -- name of the linking field in the parent table @cFKName varchar(255), -- name of the foreign key @cSQL nvarchar(1000), -- query string passed to the sp_ExecuteSQL procedure @cChildCriteria nvarchar(1000), -- criteria to be used to delete -- records from the child table @iChildRows int -- number of rows deleted from the child table -- declare the cursor containing the foreign key constraint information DECLARE cFKey CURSOR LOCAL FOR SELECT SO1.name AS Tab, SC1.name AS Col, SO2.name AS RefTab, SC2.name AS RefCol, FO.name AS FKName FROM dbo.sysforeignkeys FK INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id AND FK.fkey = SC1.colid INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id AND FK.rkey = SC2.colid INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id WHERE SO2.Name = @cTableName OPEN cFKey FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName WHILE @@FETCH_STATUS = 0 BEGIN -- build the criteria to delete rows from the child table. As it uses the -- criteria passed to this procedure, it gets progressively larger with -- recursive calls SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' + @cRefTab +'] WHERE ' + @cCriteria + ')' print 'Deleting records from table ' + @cTab -- call this procedure to delete the child rows EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName END Close cFKey DeAllocate cFKey -- finally delete the rows from this table and display the rows affected SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria print @cSQL EXEC sp_ExecuteSQL @cSQL print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName

ID: 79
PlCodelang: T-SQL
PlGroup: Datenbank, Collation
PlItemTitle: Datensatz Case-Sesitive abfragen
Code: -- Table containing Values -- content (column) -- a -- A SELECT content FROM myTable WHERE content='A' COLLATE SQL_Latin1_General_CP1_CS_AS -- Result: A

ID: 80
PlCodelang: T-SQL
PlGroup: Datenbank, Collation
PlItemTitle: Collation (Sortierreihenfolge) einer Datenbank ändern
Code: -- Collation einer Multi-User Datenbank ändern -- Über SSMS kommt die Fehlermeldung 5030 (Die Datenbank konnte nicht exklusiv gesperrt werden, um den Vorgang auszuführen.) -- siehe dazu: http://sunali.com/2009/10/08/microsoft-sql-server-error-5030/ -- the following line sets the database to "Single User" mode ALTER DATABASE MYDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE -- the following line sets the new collation ALTER DATABASE MYDB COLLATE Latin1_General_CS_AS -- the following line sets the database back to "Multi User" mode ALTER DATABASE MYDB SET MULTI_USER

ID: 81
PlCodelang: T-SQL
PlGroup: Datenbank, Collation
PlItemTitle: Collation-Arten auflisten
Code: -- siehe auch: http://msdn.microsoft.com/de-de/library/ms187963.aspx SELECT * FROM fn_helpcollations()

ID: 117
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: CSV erstellen
Code: SELECT SUBSTRING( (SELECT ',' + CSVItem FROM TableWithItems s ORDER BY s.Name FOR XML PATH('')),2,200000) GO

ID: 118
PlCodelang: T-SQL
PlGroup: Kontrollstrukturen
PlItemTitle: Switch / Case Anweisung
Code: -- http://msdn.microsoft.com/de-de/library/ms181765.aspx USE AdventureWorks2008R2; GO SELECT ProductNumber, Category = CASE ProductLine WHEN 'R' THEN 'Road' WHEN 'M' THEN 'Mountain' WHEN 'T' THEN 'Touring' WHEN 'S' THEN 'Other sale items' ELSE 'Not for sale' END, Name FROM Production.Product ORDER BY ProductNumber; GO

ID: 119
PlCodelang: T-SQL
PlGroup: Strings
PlItemTitle: Vorkommnisse eines Zeichen in einem String ermitteln
Code: DECLARE @TestString VarChar(max) = 'abcdeeeff' DECLARE @SearchFor VarChar(1) = 'e' SELECT Len(@TestString)-Len(replace(@TestString,@SearchFor,'')) As Count

ID: 122
PlCodelang: T-SQL
PlGroup: Datum, Zeit
PlItemTitle: Monat aus Datum extrahieren
Code: SELECT MONTH(GETDATE())

ID: 135
PlCodelang: T-SQL
PlGroup: Security
PlItemTitle: Zugriff auf DB-Tabellen für Schema/Package gewähren
Code: GRANT UPDATE,INSERT,SELECT,DELETE ON SCHEMA :: schema_test TO user_test

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