Manage PlItems

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.

Advanced Search

Displaying 81-90 of 300 results.

ID	PlCodelang	PlGroup	PlItemTitle	Code

67	T-SQL	Datenbank	Tabellenzeile mit FK's löschen	-- 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
295	BigQuery	Datenbank	Eine Menge B reduziert um Menge A	-- Siehe BigQuery -> Differenzen anzeigen -- https://proglang.meta-grid.com/index.php?r=plItems/view&id=294
262	MS Access SQL	Datenbank	CTAS	-- Sollte die Zieltabelle (myDestinationTable) bereits vorhanden sein -- wird diese überschrieben (neue Strukturen werden übernommen) SELECT * INTO myDestinationTable FROM mySourceTable
209	SQLite	Datum, Zeit	Kalenderwoche eines Datums	-- Standard Weeknumber function from SQLite: -- http://www.sqlite.org/lang_datefunc.html SELECT strftime('%W', '2014-08-25') -- The above calculation can lead to an wrong number if you expect an iso calendar week. -- Therefore better: -- http://stackoverflow.com/questions/15082584/sqlite-return-wrong-week-number-for-2013 SELECT (strftime('%j', date('2014-08-25', '-3 days', 'weekday 4')) - 1) / 7 + 1 AS ISOCalendarWeekNumber;
79	T-SQL	Datenbank, Collation	Datensatz Case-Sesitive abfragen	-- Table containing Values -- content (column) -- a -- A SELECT content FROM myTable WHERE content='A' COLLATE SQL_Latin1_General_CP1_CS_AS -- Result: A
213	T-SQL	Datenbank	DB-Funktion erstellen	-- 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
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
212	SQLite	Datum, Zeit	Tagesname	-- There is no build in solution. Because of this you have to build a solution out of the weekday. -- Be careful, the first weekday (0) is a sunday SELECT ,CASE CAST(strftime('%w', myDateField) as integer) WHEN 0 then 'Sonntag' WHEN 1 then 'Montag' WHEN 2 then 'Dienstag' WHEN 3 then 'Mittwoch' WHEN 4 then 'Donnerstag' WHEN 5 then 'Freitag' WHEN 6 then 'Samstag' ELSE '????????????????????' END AS Tag_der_Woche_Deutsch ,CASE CAST(strftime('%w', myDateField) as integer) WHEN 0 then 'Sunday' WHEN 1 then 'Monday' WHEN 2 then 'Tuesday' WHEN 3 then 'Wednesday' WHEN 4 then 'Thursday' WHEN 5 then 'Friday' WHEN 6 then 'Saturday' ELSE '????????????????????' END AS Day_of_week_English
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'
156	PL/pgSQL	Datum, Zeit	Monat aus Datum extrahieren	-- You get the local name for the spoken month name SELECT to_char(now(), 'TMMonth') -- results in german to "Januar" or "Dezember" (= "January", "December")