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

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

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

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

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

Parts in CTE

Common Table Expression contains three core parts

The CTE name (this is what follows the WITH keyword) The column list (optional) The query (appears within parentheses after the AS keyword) The query using the CTE must be the first query appearing after the CTE.

Use CTE in SQL Server

Syntax of CTE:With Parameter

With T(<col Name>, <col name1>, <Col name2>)  --Column names for Temporary table
AS
(
SELECT A.<Col name>, E.<col name1>, E.<col name2> from <table_name> A
INNER JOIN <table_name> E ON E.<col name> = A.<col name>
)
SELECT * FROM T  --SELECT or USE CTE temporary Table
WHERE T.[col name]  > 50
ORDER BY T.<col name>

Use CTE in SQL Server

Syntax of CTE:Without Parameter

WITH MyCTE AS (SELECT c.[Col name] FROM [table_name] pc INNER JOIN [table_name] c ON c.[Col name] = pc.[Col name]) SELECT cte.[Col name], p.[Col name] FROM [table_name] p INNER JOIN [table_name].[Col name] ea ON ea.[Col name] = p.[Col name] INNER JOIN MyCTE cte ON cte.[Col name] = p.[Col name] INNER JOIN [table_name].PersonPhone ph ON ph.[Col name] = p.[Col name];

CTE in SQL Server

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 DB Formatting

SQL DB Formatting

Use upper case for all SQL keywords  SELECT, INSERT, UPDATE, WHERE, AND, OR, LIKE, etc.  Indent code to improve readability  Comment code blocks that are not easily understandable  Use single-line comment markers(–)  Reserve multi-line comments (/*.. ..*/) for blocking out sections of code  Use single quote characters to delimit strings.  Nest single quotes to express a single quote or apostrophe within a string  For example, SET @sExample = ‘SQL”s Authority’  Use parentheses to increase readability WHERE (color=’red’ AND (size = 1 OR size = 2))  Use BEGIN..END blocks only when multiple statements are present within a conditional code segment. Use one blank line to separate code sections.  Use spaces so that expressions read like sentences.  Format JOIN operations using indents  Also, use ANSI Joins instead of old style joins   Place SET statements before any executing code in the procedure.