SQL Server – Add Column Script

–Add a column to an existing table

ALTER TABLE dbo.Employee
ADD Active varchar(1)
GO

–Add default to new column

ALTER TABLE dbo.Employee
ADD CONSTRAINT Employee_Active 
DEFAULT (‘N’) FOR Active
GO

SQL Server List Tables And Columns

–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 *
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
WHERE TABLE_CATALOG = ‘YOUR_DB_NAME’
–List Stored Procedures

SELECT *
FROM sys.procedures

–List a Stored Procedure Script

EXEC sp_HelpText N’YourStoredProc’

SQL Server Add Default To Existing Table

–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

HTML Table Example

The example below displays how to create a table using HTML and the <table> tag.

The example below consists of the following elements:
<table> This tag begins the table.

border = “1” This attribute defines the width of the border. If this attribute is not added to the table tag, no border will be displayed.
<tr> This tag begins a row in the table.

<td> This tag defines a column in the table, and stands for table data.

<th> This tag defines a table header. All text within this tag will be displayed in bold.

align = “right” This attribute is used below in the <td> tag to align the data displayed to the right of the column.

<table border=”1″>
<tr>
<th>Grade</th>
<th>Student Count</th>
</tr>
<tr>
<td>A</td>
<td align=”right”>10</td>
</tr>
<tr>
<td>B</td>
<td align=”right”>11</td>
</tr>
<tr>
<td>C</td>
<td align=”right”>7</td>
</tr>
<tr>
<td>D</td>
<td align=”right”>3</td>
</tr>
<tr>
<td>F</td>
<td align=”right”>2</td>
</tr>
</table>

The code above produces the following table:

Grade Student Count
A 10
B 11
C 7
D 3
F 2

Create a Table From a SQL Statement

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 FROM TABLE1 a LEFT OUTER JOIN TABLE2 b ON a.field1 = b.field5

The newly created table will not have a primary key even if the existing table or tables do have a primary key.

SQL Server DatePart Example


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’
WHEN datepart(weekday,getdate()) = 5 THEN ‘Thursday’
WHEN datepart(weekday,getdate()) = 6 THEN ‘Friday’
WHEN datepart(weekday,getdate()) = 7 THEN ‘Saturday’END


DECLARE @Date1 datetime


SET @Date1 = ’01/01/2020′


SELECT


CASE WHEN datepart(weekday,@Date1) = 1 THEN ‘Sunday’
WHEN datepart(weekday,@Date1) = 2 THEN ‘Monday’
WHEN datepart(weekday,@Date1) = 3 THEN ‘Tuesday’
WHEN datepart(weekday,@Date1) = 4 THEN ‘Wednesday’
WHEN datepart(weekday,@Date1) = 5 THEN ‘Thursday’
WHEN datepart(weekday,@Date1) = 6 THEN ‘Friday’
WHEN datepart(weekday,@Date1) = 7 THEN ‘Saturday’END


–Date Format DDD, MMM, DD, YYYY
DECLARE @Date2 datetime


SET @Date2 = ’01/01/2020′


SELECT


CASE WHEN datepart(weekday,@Date2) = 1 THEN ‘Sun’
WHEN datepart(weekday,@Date2) = 2 THEN ‘Mon’
WHEN datepart(weekday,@Date2) = 3 THEN ‘Tue’
WHEN datepart(weekday,@Date2) = 4 THEN ‘Wed’
WHEN datepart(weekday,@Date2) = 5 THEN ‘Thu’
WHEN datepart(weekday,@Date2) = 6 THEN ‘Fri’
WHEN datepart(weekday,@Date2) = 7 THEN ‘Sat’END
+ ‘, ‘ + convert(nvarchar(3),@Date2, 107) + ‘, ‘ + convert(nvarchar(2),@Date2,113) + ‘, ‘ + convert(nvarchar(4),year(@Date2))


select datepart(month,getdate())


select datepart(day,getdate())


select datepart(year,getdate())


select datepart(week,getdate())


select datepart(weekday,getdate())


select datepart(hour,getdate())


select datepart(minute,getdate())


select datepart(second,getdate())


select datepart(millisecond,getdate())


Facebook Tip – View Photos The ‘Old’ Way (Without the Pop Up)

Facebook recently changed the way you view photos.  By default, when you click on a photo, the photo pop ups over the Facebook page.  Previously, when you clicked on a photo, you would be directed to another Facebook page with the photo displayed on the page and previous and next links above the photo.

If you preferred the old way Facebook displayed photos, do the following:

Right Click on the picture.
Select Open link in new tab with the left mouse button.

That is it, you can now view photos without the pop up.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: