Find All Monday in Current and Previous Year

Find All Monday in Current and Previous Year

SELECT Mondays = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), n.num) FROM (SELECT TOP 732 num = ROW_NUMBER() OVER(ORDER BY a.NAME)-366 FROM dbo.syscolumns a, dbo.syscolumns b) n WHERE DATENAME(weekday, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), n.num)) = 'Monday'

Find All Monday in Current Year

Find All Monday in Current Year

SELECT Mondays = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), n.num) FROM (SELECT TOP 366 num = ROW_NUMBER() OVER(ORDER BY a.NAME)-1 FROM dbo.syscolumns a, dbo.syscolumns b) n WHERE DATENAME(weekday, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), n.num)) = 'Monday'

Find All Fridays in Current AND Next Year

Find All Fridays in Current AND Next Year

SELECT Fridays = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), n.num) FROM (SELECT TOP 732 num = ROW_NUMBER() OVER(ORDER BY a.NAME)-1 FROM dbo.syscolumns a, dbo.syscolumns b) n WHERE DATENAME(weekday, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), n.num)) = 'Friday'

Find All Fridays in Current Year

Find All Fridays in Current Year

SELECT Fridays = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), n.num) FROM (SELECT TOP 366 num = ROW_NUMBER() OVER(ORDER BY a.NAME)-1 FROM dbo.syscolumns a, dbo.syscolumns b) n WHERE DATENAME(weekday, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), n.num)) = 'Friday'

Get Only Time from datetime 2008

get Only Time from datetime 2008

CONVERT(time,GETDATE(),108) AS HourMinuteSecond

Get Only Date from datetime in SQL Server 2008

get Only Date from datetime in SQL Server 2008

CONVERT(Date,GETDATE(),101) AS DateOnly

Get Only Time from datetime 2005

get Only Time from datetime 2005

CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond

IIF funciton in SQL Server 2012 and give an Example

If you are using SQL Server 2012 you can use IIF and get the same effect as CASE statement.
Create a Table

CREATE TABLE SimpleTable (ID INT, NAME VARCHAR(10))
GO
Insert some value into table
INSERT INTO SimpleTable (ID, NAME)
SELECT 1, 'LAKSHMI'
UNION ALL
SELECT 2, 'NARAYANAN'
UNION ALL
SELECT 3, 'NARAYANAN'
GO

IIF Statement

UPDATE SimpleTable
SET Gender = IIF(NAME = 'NARAYANAN', 'LAKSHMI', 'NARAYANAN')
GO
SELECT *
FROM SimpleTable
GO

Example for CASE Function in SQL

Create a Table

CREATE TABLE SimpleTable (ID INT, NAME VARCHAR(MAX))
GO
Insert some value into table
INSERT INTO SimpleTable (ID, NAME)
SELECT 1, 'LAKSHMI'
UNION ALL
SELECT 2, 'NARAYANAN'
UNION ALL
SELECT 3, 'NARAYANAN'
GO

CASE Funciton:

UPDATE SimpleTable
SET Gender = CASE NAME WHEN 'NARAYANAN' THEN 'LAKSHMI' ELSE 'NARAYANAN' END
GO
SELECT *
FROM SimpleTable
GO

Example for REPLACE Function in SQL

Create a Table

CREATE TABLE SimpleTable (ID INT, Gender VARCHAR(10))
GO
Insert some value into table
INSERT INTO SimpleTable (ID, Gender)
SELECT 1, 'female'
UNION ALL
SELECT 2, 'male'
UNION ALL
SELECT 3, 'male'
GO

Replace male into female using UPDATE Query

UPDATE SimpleTable
SET Gender = REPLACE(('fe'+Gender),'fefe','')
GO
SELECT *
FROM SimpleTable
GO

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND name = '<Sp_name>'

Get Current Date in SQL Server 2005

get Only Date from datetime in SQL Server 2005

GETDATE()
CONVERT(VARCHAR(10),GETDATE(),101) AS DateOnly

Column Exists or Not in SQL Server

Column Exists or Not in SQL Server

CREATE FUNCTION Axfn_ColumnExists
(
 @TableName VARCHAR(100) ,
@ColumnName VARCHAR(100) )
RETURNS VARCHAR(100)
 AS
BEGIN
 DECLARE @Result VARCHAR(100);
 IF EXISTS
(
 SELECT 1 FROM INFORMATION_SCHEMA.Columns
              WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName ) BEGIN
 SET @Result = 'Already Exsits'
 END
 ELSE
 BEGIN SET @Result = 'Not Available, You can create now!'
 END RETURN (@Result)
 END
GO

SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%<search_word>%'

CTE in SQL Server

the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'Name Your Column Here' + '%'
AND TYPE = 'P'

SQL Constraints

SQL constraints are used to specify rules for the data in a table.
 If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Syntax for SQL Constraints
SQL CREATE TABLE + CONSTRAINT Syntax

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

 In SQL, we have the following constraints:

NOT NULL - Indicates that a column cannot store NULL value
UNIQUE - Ensures that each row for a column must have a unique value
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
CHECK - Ensures that the value in a column meets a specific condition
DEFAULT - Specifies a default value when specified none for this column

SQL SERVER – Disable All the Foreign Key Constraint in Database – Enable All the Foreign Key Constraint in Database

-- Disable all the constraint in database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- Enable all the constraint in database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Refer this link

SQL SERVER – Create Unique Constraint on Table Column on Existing Table

by using UNIQUE for Unique constraint.

Syntax:

   UNIQUE([col name])

Example:

USE tempdb
GO
-- Create Table
CREATE TABLE Table1 (ID INT, Col1 VARCHAR(100))
GO
-- Alter Table Create Constraint
ALTER TABLE Table1
ADD CONSTRAINT UX_Constraint UNIQUE (Col1)
GO
-- Clean up
DROP TABLE Table1
GO