| Find a column in all tables | USE dbname GO SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE '%Code_Id%' ORDER BY schema_name, table_name; | Replace "dbname" with database name and replace "Code_Id" with column name you wish to search for. The query will return results of all tables with the column name. |
| Import from file | sqlcmd -S instance -d dbname -i filename | This is done from the command prompt using sqlcmd.exe. -S is for the servername\instance_name and -d is the database to switch to. In the case where there is no instance then the default is used and only the servername is required. |
| Select Insert Into | INSERT INTO tablename1 SELECT column1, column2, column3 FROM tablename2 | |
| Reset Identity Seed (auto increment) | dbcc checkident (YourTable,reseed,<insert_number>) | Replace with "0" (zero) to start incrementing from "1" (one). |
| Select linked database | SELECT * FROM OPENQUERY(linkname, 'SELECT column1, column2, column3 FROM tablename) | |
| Show Process List | SELECT * FROM master..sysprocesses ORDER BY cpu desc | |
| Kill Process | kill spid | Replace "spid" with the applicable value from the show process list query. |
| Case, Substring and Charindex Example | UPDATE Items SET LogoName= CASE WHEN CHARINDEX('_100_white', LogoName, 0) > 0 THEN SUBSTRING(LogoName, 0, CHARINDEX('_100_white', LogoName, 0)) + '.jpg' ELSE LogoName END | Case, if like logic for SQL Server 2000. Substring(), return a portion out of a string. Charindex(), find start position of a string within a string. |
| Delete Date Range | DELETE FROM teleguide.teleguide_error WHERE (error_time >= CONVERT(VARCHAR(8), GETDATE(), 1) + ' 00:00:00') AND (error_time <= CONVERT(VARCHAR(8), GETDATE(), 1) + ' 23:59:00') | |
| SQL Server Version | SELECT @@VERSION | Returns system and build information for the current installation of the SQL Server. |