Tag: SQL

Before clicking the button to execute an update or delete query on a table in Microsoft SQL Server Management Studio, have you ever asked yourself, Do I really want to do this? I know I have. This handy statement creates a copy of the table you specify so that you don’t lose all of your data if something goes awry.

SELECT *
INTO {%new table name%}
FROM {%existing table name%}

When working on an update query, I prefer to run it first on a test database to make sure it does what I think it’s going to do—and to make sure I haven’t made a simple mistake. When that’s not possible, however, there is another approach that works pretty well, too: select, then update.

First, I run a query like this with the update portions commented out:

SELECT *
FROM customers
--UPDATE customers
--SET title = 'Mr.'
WHERE id > 123 and id < 150
ORDER BY id

After I’ve checked the results to ensure I’ll be affecting only the records I intend to change, I comment out the select, from, and order by bits, and uncomment the update and set lines:

--SELECT *
--FROM customers
UPDATE customers
SET title = 'Mr.'
WHERE id > 123 and id < 150
--ORDER BY id

By taking this approach, I’m less likely to accidentally update records I didn’t intend to touch.

Last week, one of my coworkers was trying to figure out which tables in a database contained a particular column name. That’s something I’ve had to do in the past, and although I didn’t remember the exact details, a quick search turned up this gem from a Stack Overflow thread:

SELECT column_name, table_name FROM information_schema.columns WHERE column_name like '%{column name}%'

This query returns a list of all tables and views in the database that have column names containing the name for which you are searching.

If you know the exact name of the column, you can change the where clause to WHERE column_name = '{column name}'.