Auto increment table column

Postgres 10 or later Consider a standard-SQL IDENTITY column. serial columns remain unchanged. (See below.) But the former is preferable in modern Postgres. Can be GENERATED BY DEFAULT or (stricter) GENERATED ALWAYS. Basics in the manual for CREATE TABLE. Details in this blog entry by its principal author Peter Eisentraut. Create table with IDENTITY column … Read more

MySQL – Select from a list of numbers those without a counterpart in the id field of a table

This is a problem that is pretty common: generating a relation on the fly without creating a table. SQL solutions for this problem are pretty awkward. One example using a derived table: SELECT n.id FROM (SELECT 2 AS id UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) AS … Read more

Check if extended property description already exists before adding

This first script checks if the extended property describing the table exists: IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID(‘Table_Name’) AND [name] = N’MS_Description’ AND [minor_id] = 0) EXECUTE sp_addextendedproperty @name = N’MS_Description’, @value = N’This table is responsible for holding information.’, @level0type = N’SCHEMA’, @level0name = N’dbo’, @level1type = N’TABLE’, @level1name … Read more

SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)

This functionality is already built in to Sql Server Management Studio 2008. Just download the trial and only install the client tools (which shouldn’t expire). Use Management Studio 2008 to connect to your 2005 database (its backwards compatible). Right click your database Choose Tasks > Generate Scripts Press Next, select your database again On the … Read more