–Add a column to an existing table ALTER TABLE dbo.EmployeeADD Active varchar(1)GO –Add default to new column ALTER TABLE dbo.EmployeeADD CONSTRAINT Employee_Active DEFAULT (‘N’) FOR ActiveGO
–List Column Names SELECT COLUMN_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = ‘YOUR_DB_NAME’ –List Tables SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = ‘YOUR_DB_NAME’ –List Views SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_CATALOG = ‘YOUR_DB_NAME’ –List All Columns in All Views SELECT *…
–String Value ALTER TABLE [Table1] ADD CONSTRAINT [Table1_Column1] DEFAULT (‘Not Applicable’) FOR [Column1] GO –Numeric Value ALTER TABLE [Table1] ADD CONSTRAINT [Table1_Column1] DEFAULT ((0)) FOR [Column1] GO –Date Value ALTER TABLE [Table1] ADD CONSTRAINT [Table1_Column1] DEFAULT (((1)/(1))/(1900)) FOR [Column1] GO
You can create a copy of a table using the SELECT INTO command. SELECT * INTO TABLE2 FROM TABLE1 You can also join two or more tables and create a new table from the joined tables. SELECT * INTO TABLE3…
–Get Current Date and Time select getdate() –Date Format MMM DD YYYY select convert(nvarchar(12),getdate(),100) –Date Format MM/DD/YYYY select convert(nvarchar(10),getdate(),101) –Date Format YYYY.MM.DD select convert(nvarchar(10),getdate(),102) –Date Format DD/MM/YYYY select convert(nvarchar(10),getdate(),103) –Date Format DD.MM.YYYY select convert(nvarchar(10),getdate(),104) –Date Format DD-MM-YYYY select convert(nvarchar(10),getdate(),105) –Date…
Select Top N from SQL Server Oracle RowNum pseudocolumn. SQL Server and Oracle allow you to run a Select Statement, but limit the number of rows returned. This is very useful if you just need to see a snapshot of…
REPLACE STATEMENT The Replace Statement replaces all instances of a specified string value with a new value. Syntax: REPLACE(Field Name, Search String, New String) SQL Server: SELECT REPLACE(FIELD1,’/’,’-‘) FROM TABLE Oracle: SELECT REPLACE(FIELD1,’/’,’-‘) FROM TABLE;
Character Function – Returns the Character from a given character code SQL Server SELECT CHAR(65) + CHAR(66) + CHAR(67) Returns ABC Oracle: SELECT CHR(65) || CHR(66) || CHR(67) FROM dual; Returns ABC
Character Function – Returns the Character from a given character code SQL Server SELECT CHAR(65) + CHAR(66) + CHAR(67) Returns ABC Oracle: SELECT CHR(65) || CHR(66) || CHR(67) FROM dual; Returns ABC
Concatenate Note, when joining two or more fields together, both fields must be a string value. If one of the fields is a numeric value, the numeric field must be converted to a string. SQL Server: SELECT Emp_LastName + ‘,…
Tech and Programming Tips 