–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
select datepart(quarter,getdate()) –Get Week Day Numerical value, Sunday = 1st day of week SELECT CASE WHEN datepart(weekday,getdate()) = 1 THEN ‘Sunday’ WHEN datepart(weekday,getdate()) = 2 THEN ‘Monday’ WHEN datepart(weekday,getdate()) = 3 THEN ‘Tuesday’ WHEN datepart(weekday,getdate()) = 4 THEN ‘Wednesday’…
–Date Difference Function –Number of days between two dates select datediff(day,getdate(),’09/01/2020′) –Number of months between two dates select datediff(month,getdate(),’09/01/2020′) –Number of years between two dates select datediff(year,getdate(),’09/01/2020′)
–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…
Tech and Programming Tips 