Skip to content

Microsoft SQL Server Quick Reference

Task Command Example Notes
Find a column in all tables USE dbname GO SELECT AS table_name, SCHEMA_NAME(schema_id) AS schema_name, AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE 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.