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.

SQL Server DateDiff Function

–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′)

SQL Server Date Formatting and Date Examples

–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 Format DD MMM YYYY
select convert(nvarchar(11),getdate(),106)

–Date Format MMM DD, YYYY
select convert(nvarchar(12),getdate(),107)

–Date Format hh:mm:ss (24hr)
select convert(nvarchar(10),getdate(),108)

–Date Format hh:mm:ss:mmm (AM PM)
select convert(nvarchar(30),getdate(),109)

–Date Format MM-DD-YYYY (AM PM)
select convert(nvarchar(10),getdate(),110)

–Date Format YYYY/MM/DD
select convert(nvarchar(10),getdate(),111)

–Date Format YYYYMMDD
select convert(nvarchar(10),getdate(),112)

–Date Format DD MMM YYYY hh:mm:ss:mmm(24h)
select convert(nvarchar(24),getdate(),113)

–Date Format hh:mm:ss:mmm(24h)
select convert(nvarchar(12),getdate(),114)

–Date Format hh:mm:ss:mmm(24h)
select convert(nvarchar(19),getdate(),120)

–Date Format YYYY-MM-DD hh:mm:ss:mmm(24h)
select convert(nvarchar(23),getdate(),121)

–Date Format YYYY-MM-DDThh:mm:ss:mmm(24h)
select convert(nvarchar(24),getdate(),126)

–Time Format hh:mm PM
select substring(convert(varchar(20), GetDate(), 109), 13, 5) + ‘ ‘ + substring(convert(varchar(30), GetDate(), 109), 25, 2)

–Time Format hh:mm:ss PM
select substring(convert(varchar(20), GetDate(), 109), 13, 8) + ‘ ‘ + substring(convert(varchar(30), GetDate(), 109), 25, 2)

–Date Format YYYY.MM.DD
select convert(nvarchar(10),getdate(),102)

–Date Get Month
select month(getdate())

–Date Get Numerical Day of Month
select day(getdate())

–Date Get Year
select year(getdate())

–Date Get Last Day of Current Month
select convert(nvarchar(10),dateadd(day,-1,convert(nvarchar(2),month(dateadd(month,1,getdate()))) + ‘/1/’ + convert(nvarchar(4),year(dateadd(month,1,getdate())))),101)

–Date Get First Day of Current Month
select convert(nvarchar(10),cast(convert(varchar(2),month(getdate())) + ‘/01/’ + convert(varchar(4),year(getdate())) as datetime),101)

–Date Get Last Day of Current Year
select ’12/31/’ + convert(nvarchar(4),year(dateadd(month,1,getdate())))

–Date Get First Day of Current Year
select ’01/01/’ + convert(varchar(4),year(getdate()))

Visual Studio: Display A File List In A DataGridView Control

This example also shows how to extract an icon image from a file in a windows directory and display the icon in a DataGridView Control.  One advantage of displaying a list of files in the DataGridView Control, is that you can take advantage of the built in sort function and the automatic ability to Select All and Copy built into the DataGridView Control.  The functionality can be accessed by clicking ‘Ctl + A’
and ‘Ctl + C’.  After you have copied the list, you can paste into Excel or Notepad.

First Create a new Visual Studio C# Windows Application Solution.
Drag and Drop a DataGridView Control onto the form.
Right Click on the DataGridView Control and Click Properties.
Select ‘Docking’ in the Properties and select the middle square.
Click on the arrow on the DataGridView Control and Uncheck:

Enable Adding
Enable Deleting
Enable Editing

Copy and paste the code below into the load event of the form:

            dataGridView1.AllowUserToAddRows = false;
            dataGridView1.RowHeadersVisible = false;

            DataGridViewImageColumn colIcon = new DataGridViewImageColumn();
            colIcon.Name = "colIcon";
            colIcon.HeaderText = "";
            colIcon.ImageLayout = DataGridViewImageCellLayout.Zoom;
            colIcon.DefaultCellStyle.SelectionBackColor = Color.White;
            colIcon.Width = 18;
            //colIcon.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            dataGridView1.Columns.Add(colIcon);

            DataGridViewColumn colHold = new DataGridViewTextBoxColumn();
            colHold.Name = "colFileName";
            colHold.HeaderText = "Name";
            colHold.DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft;
            colHold.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            dataGridView1.Columns.Add(colHold);

            colHold = new DataGridViewTextBoxColumn();

            colHold.Name = "colFileSize";
            colHold.HeaderText = "Size";
            colHold.DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight;
            colHold.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            dataGridView1.Columns.Add(colHold);

            colHold = new DataGridViewTextBoxColumn();

            colHold.Name = "colFileType";
            colHold.HeaderText = "Extension";
            colHold.DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft;
            colHold.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            dataGridView1.Columns.Add(colHold);

            colHold = new DataGridViewTextBoxColumn();

            colHold.Name = "colDateModified";
            colHold.HeaderText = "Date Modified";
            colHold.DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight;
            colHold.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            dataGridView1.Columns.Add(colHold);

            dataGridView1.RowTemplate.Height = 18;

            //Replace the directory below with a directory on your computer.
            string strDirLocal = @"C:\documents";
            int nRow = 0;
            string sFileName = "";
            decimal nFileSize = 0;
            string sExt = "";
            string sDateModified = "";

            if (System.IO.Directory.Exists(strDirLocal))
            {                
                foreach (string sPath in System.IO.Directory.GetFiles(strDirLocal, "*.*"))
                {
                    //http://msdn.microsoft.com/en-us/library/ms404308.aspx
                    Icon FileIcon = SystemIcons.WinLogo;
                    FileIcon = Icon.ExtractAssociatedIcon(sPath);
                    //Add the file to the TextBox, and remove the Path from the sPath string,
                    //leaving the file name.
                    dataGridView1.Rows.Add();
                    nRow = dataGridView1.Rows.Count - 1;
                    sFileName = sPath.Replace(strDirLocal + @"\", "");
                    sDateModified = System.IO.File.GetLastWriteTime(sPath).ToString();
                    //sFileSize = System.IO.File.ReadAllBytes(sPath).ToString();
                    nFileSize = System.IO.File.ReadAllBytes(sPath).Length;
                    nFileSize = nFileSize / 1024;
                    nFileSize = System.Math.Round(nFileSize);
                    if (nFileSize < 1)
                    {
                        nFileSize = 1;
                    }
                    if (System.IO.File.ReadAllBytes(sPath).Length == 0)
                    {
                        nFileSize = 0;
                    }
                    string[] sExtHold = sPath.Split('.');
                    sExt = sExtHold[sExtHold.Count()-1];
                    //http://www.codeproject.com/KB/grid/ImagePreviewDataGridView.aspx

                    dataGridView1.Rows[nRow].Cells[0].Value = FileIcon;                                        
                    dataGridView1.Rows[nRow].Cells[1].Value = sFileName;
                    dataGridView1.Rows[nRow].Cells[2].Value = nFileSize;
                    dataGridView1.Rows[nRow].Cells[3].Value = sExt;
                    dataGridView1.Rows[nRow].Cells[4].Value = sDateModified;                    
                }
            }

Please note, if a file is open in the directory, you will receive an error.  You should add error handling to account for this error.

Related Links:

http://msdn.microsoft.com/en-us/library/ms404308.aspx

http://www.codeproject.com/KB/grid/ImagePreviewDataGridView.aspx

Export To A Text File From A DataGridView Control

Create a HTML File From a DataGridView Control

Display A Text File In A DataGridView

Display A Directory List In A TextBox Control