Most databases support != (popular programming languages) and <> (ANSI). Databases that support both != and <>: MySQL 5.1: != and <> PostgreSQL 8.3: != and <> SQLite: != and <> Oracle 10g: != and <> Microsoft SQL Server 2000/2005/2008/2012/2016: != and <> IBM Informix Dynamic Server 10: != and <> InterBase/Firebird: != and <> … Read more
They are not the same thing. Consider these queries: SELECT * FROM Orders LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID WHERE Orders.ID = 12345 and SELECT * FROM Orders LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID AND Orders.ID = 12345 The first will return an order and its lines, if any, for order number 12345. The second will return … Read more
Rather slow, but working method to include any of words: SELECT * FROM mytable WHERE column1 LIKE ‘%word1%’ OR column1 LIKE ‘%word2%’ OR column1 LIKE ‘%word3%’ If you need all words to be present, use this: SELECT * FROM mytable WHERE column1 LIKE ‘%word1%’ AND column1 LIKE ‘%word2%’ AND column1 LIKE ‘%word3%’ If you want … Read more
The DBCC CHECKIDENT management command is used to reset identity counter. The command syntax is: DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}]) [ WITH NO_INFOMSGS ] Example: DBCC CHECKIDENT (‘[TestTable]’, RESEED, 0); GO It was not supported in previous versions of the Azure SQL Database but is supported now. Thanks … Read more
SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description FROM Orders JOIN LineItems ON LineItems.LineItemGUID = ( SELECT TOP 1 LineItemGUID FROM LineItems WHERE OrderID = Orders.OrderID ) In SQL Server 2005 and above, you could just replace INNER JOIN with CROSS APPLY: SELECT Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description FROM Orders CROSS APPLY ( SELECT TOP 1 LineItems.Quantity, LineItems.Description FROM LineItems WHERE … Read more
Not sure why no one suggested but I use sp_fkeys to query foreign keys for a given table: EXEC sp_fkeys ‘TableName’ You can also specify the schema: EXEC sp_fkeys @pktable_name=”TableName”, @pktable_owner=”dbo” Without specifying the schema, the docs state the following: If pktable_owner is not specified, the default table visibility rules of the underlying DBMS apply. … Read more
You can obtain this information and much, much more by querying the Information Schema views. This sample query: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N’Customers’ Can be made over all these DB objects: CHECK_CONSTRAINTS COLUMN_DOMAIN_USAGE COLUMN_PRIVILEGES COLUMNS CONSTRAINT_COLUMN_USAGE CONSTRAINT_TABLE_USAGE DOMAIN_CONSTRAINTS DOMAINS KEY_COLUMN_USAGE PARAMETERS REFERENTIAL_CONSTRAINTS ROUTINES ROUTINE_COLUMNS SCHEMATA TABLE_CONSTRAINTS TABLE_PRIVILEGES TABLES VIEW_COLUMN_USAGE VIEW_TABLE_USAGE VIEWS
Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed). A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set. A point worth noting from comments, which summarize the … Read more
You can try it this way: /* Get the data into a temp table */ SELECT * INTO #TempTable FROM YourTable /* Drop the columns that are not needed */ ALTER TABLE #TempTable DROP COLUMN ColumnToDrop /* Get results and drop temp table */ SELECT * FROM #TempTable DROP TABLE #TempTable
Escape the square brackets: … WHERE m.definition Like ‘%\[ABD\]%’ ESCAPE ‘\’ Then the square brackets will be treated as a string literals not as wild cards.