How to search stored procedures containing a particular text?

select *
      from information_schema.routines
      where routine_definition like '%employee_id%'
      and routine_type='procedure'

How I find a particular column name within all tables of SQL server database?

Find a particular column name within all tables of SQL server database.

select * from information_schema.columns
where column_name like '%employee_id%'



Find a particular column name and datatype details within all tables of SQL server database.

SELECT
OBJECT_NAME(c.OBJECT_ID) TableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.PRECISION
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id where c.name like '%family_id%'

ORDER BY c.OBJECT_ID;

How to change datatype of an existing column in sql

ALTER TABLE dbo.tblEmployee
ALTER COLUMN status bit null

bit datatype in SQL Server

APPLIED FOR: SQL Server (starting with 2008 And above version)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse

An integer data type that can take a value of 1, 0, or NULL.

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.

The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

Converting to bit promotes any nonzero value to 1.

Effective ways of writing stored procedure in sql server

Improve stored procedure performance in SQL Server

1. Use SET NOCOUNT ON. ...
2. Use fully qualified procedure name. ...
3. sp_executesql instead of Execute for dynamic queries. ...
4. Using IF EXISTS AND SELECT. ...
5. Avoid naming user stored procedure as sp_procedurename. ...
6. Use set based queries wherever possible. ...
7. Keep transaction short and crisp.

Terms Used in Stored Procedures

SET ANSI_NULLS ON

SET ANSI_NULLS ON/OFF: The ANSI_NULLS option specifies that how SQL Server handles the comparison operations with NULL values. When it is set to ON any comparison with NULL using = and <> will yield to false value. And it is the ISO defined standard behavior.

SET NOCOUNT ON

When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned. It is used with any SELECT, INSERT, UPDATE, DELETE statement. The setting of SET NOCOUNT is set at execute or run time and not at parse time.

SET QUOTED_IDENTIFIER ON

Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers.

Syntax

-- Syntax for SQL Server and Azure SQL Database  

SET QUOTED_IDENTIFIER { ON | OFF }  

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  

SET QUOTED_IDENTIFIER ON   

Remarks

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. For more information. Literals can be delimited by either single or double quotation marks.

When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not generally allowed in Transact-SQL identifiers. Double quotation marks cannot be used to delimit literal string expressions; single quotation marks must be used to enclose literal strings. If a single quotation mark (') is part of the literal string, it can be represented by two single quotation marks ("). SET QUOTED_IDENTIFIER must be ON when reserved keywords are used for object names in the database.

When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.

SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET Statements

SET QUOTED_IDENTIFIER must be ON when you are creating a filtered index.

SET QUOTED_IDENTIFIER must be ON when you invoke XML data type methods.

The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set QUOTED_IDENTIFIER to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties. The default for SET QUOTED_IDENTIFIER is OFF for connections from DB-Library applications.

When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the table's metadata even if the option is set to OFF when the table is created.

When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure.

When executed inside a stored procedure, the setting of SET QUOTED_IDENTIFIER is not changed.

When SET ANSI_DEFAULTS is ON, SET QUOTED_IDENTIFIER is enabled.

SET QUOTED_IDENTIFIER also corresponds to the QUOTED_IDENTIFIER setting of ALTER DATABASE. For more information about database settings.

SET QUOTED_IDENTIFIER is takes effect at parse-time and only affects parsing, not query execution.

For a top-level Ad-Hoc batch parsing begins using the session’s current setting for QUOTED_IDENTIFIER. As the batch is parsed any occurrence of SET QUOTED_IDENTIFIER will change the parsing behavior from that point on, and save that setting for the session. So after the batch is parsed and executed, the session’s QUOTED_IDENTIFER setting will be set according to the last occurrence of SET QUOTED_IDENTIFIER in the batch.
Static SQL in a stored procedure is parsed using the QUOTED_IDENTIFIER setting in effect for the batch that created or altered the stored procedure. SET QUOTED_IDENTIFIER has no effect when it appears in the body of a stored procedure as static SQL.

For a nested batch using sp_executesql or exec(), the parsing begins using the QUOTED_IDENTIFIER setting of the session. If the nested batch is inside a stored procedure the parsing starts using the QUOTED_IDENTIFIER setting of the stored procedure. As the nested batch is parsed any occurrence of SET QUOTED_IDENTIFIER will change the parsing behavior from that point on, but the session’s QUOTED_IDENTIFIER setting will not be updated.

Using brackets, [ and ], to delimit identifiers is not affected by the QUOTED_IDENTIFIER setting.

To view the current setting for this setting, run the following query.

DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF';  
IF ( (256 & @@OPTIONS) = 256 ) SET @QUOTED_IDENTIFIER = 'ON';  
SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER;  

Permissions

Requires membership in the public role.

Examples

A. Using the quoted identifier setting and reserved word object names

The following example shows that the SET QUOTED_IDENTIFIER setting must be ON, and the keywords in table names must be in double quotation marks to create and use objects that have reserved keyword names.

SET QUOTED_IDENTIFIER OFF  
GO  
-- An attempt to create a table with a reserved keyword as a name  
-- should fail.  
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);  
GO  

SET QUOTED_IDENTIFIER ON;  
GO  

-- Will succeed.  
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);  
GO  

SELECT "identity","order"   
FROM "select"  
ORDER BY "order";  
GO  

DROP TABLE "SELECT";  
GO  

SET QUOTED_IDENTIFIER OFF;  
GO  

B. Using the quoted identifier setting with single and double quotation marks

The following example shows the way single and double quotation marks are used in string expressions with SET QUOTED_IDENTIFIER set to ON and OFF.

SET QUOTED_IDENTIFIER OFF;  
GO  
USE AdventureWorks2012;  
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  
      WHERE TABLE_NAME = 'Test')  
   DROP TABLE dbo.Test;  
GO  
USE AdventureWorks2012;  
CREATE TABLE dbo.Test (ID INT, String VARCHAR(30)) ;  
GO  

-- Literal strings can be in single or double quotation marks.  
INSERT INTO dbo.Test VALUES (1, "'Text in single quotes'");  
INSERT INTO dbo.Test VALUES (2, '''Text in single quotes''');  
INSERT INTO dbo.Test VALUES (3, 'Text with 2 '''' single quotes');  
INSERT INTO dbo.Test VALUES (4, '"Text in double quotes"');  
INSERT INTO dbo.Test VALUES (5, """Text in double quotes""");  
INSERT INTO dbo.Test VALUES (6, "Text with 2 """" double quotes");  
GO  

SET QUOTED_IDENTIFIER ON;  
GO  

-- Strings inside double quotation marks are now treated   
-- as object names, so they cannot be used for literals.  
INSERT INTO dbo."Test" VALUES (7, 'Text with a single '' quote');  
GO  

-- Object identifiers do not have to be in double quotation marks  
-- if they are not reserved keywords.  
SELECT ID, String   
FROM dbo.Test;  
GO  

DROP TABLE dbo.Test;  
GO  

SET QUOTED_IDENTIFIER OFF;  
GO  

Here is the result set.

ID          String 
----------- ------------------------------ 
1           'Text in single quotes' 
2           'Text in single quotes' 
3           Text with 2 '' single quotes 
4           "Text in double quotes" 
5           "Text in double quotes" 
6           Text with 2 "" double quotes 
7           Text with a single ' quote

How to drop and create a stored procedure in SQL Server

USE [SampleDB]
GO

DROP PROCEDURE [dbo].[Sp_UpdateEmployee]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Sp_UpdateEmployee]
AS
BEGIN
  DECLARE @CurrentRowCount int

  BEGIN TRY
    BEGIN TRANSACTION

      SELECT
        @CurrentRowCount = (SELECT
          COUNT(*)
        FROM Employee
        WHERE status = 0
        AND updated_on < DATEADD(dd, -90,
        GETDATE()))

      WHILE (@CurrentRowCount > 0)
      BEGIN
        UPDATE Employee
        SET status = 4
        WHERE updated_on < DATEADD(dd, -90, GETDATE())
      END

    COMMIT TRANSACTION
  END TRY

  BEGIN CATCH
    ROLLBACK TRANSACTION
  END CATCH
END


GO

How to drop a stored procedure in SQL Server

USE [SampleDB]
GO

DROP PROCEDURE [dbo].[Sp_UpdateEmployee]
GO

How to alter a stored procedure in SQL Server

USE [SampleDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Sp_UpdateEmployee]
AS
BEGIN
  DECLARE @CurrentRowCount int

  BEGIN TRY
    BEGIN TRANSACTION

      SELECT
        @CurrentRowCount = (SELECT
          COUNT(*)
        FROM Employee
        WHERE status = 0
        AND updated_on < DATEADD(dd, -90,
        GETDATE()))

      WHILE (@CurrentRowCount > 0)
      BEGIN
        UPDATE Employee
        SET status = 4
        WHERE updated_on < DATEADD(dd, -90, GETDATE())
      END

    COMMIT TRANSACTION
  END TRY

  BEGIN CATCH
    ROLLBACK TRANSACTION
  END CATCH
END


GO

How to create a stored procedure in SQL Server

USE [SampleDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Sp_UpdateEmployee]
AS
BEGIN
  DECLARE @CurrentRowCount int

  BEGIN TRY
    BEGIN TRANSACTION

      SELECT
        @CurrentRowCount = (SELECT
          COUNT(*)
        FROM Employee
        WHERE status = 0
        AND updated_on < DATEADD(dd, -90,
        GETDATE()))

      WHILE (@CurrentRowCount > 0)
      BEGIN
        UPDATE incite_brand_expression
        SET status = 4
        WHERE updated_on < DATEADD(dd, -90, GETDATE())
      END
    COMMIT TRANSACTION
  END TRY

  BEGIN CATCH
    ROLLBACK TRANSACTION
  END CATCH
END


GO

How to display all the Procedures, Function, Triggers and other objects those are present in the SQL Server DB and its details.

How to display all the Procedures, Function, Triggers and other objects those are present in the SQL Server DB and its details.

SELECT SPECIFIC_NAME,ROUTINE_DEFINITION from information_schema.routines

Use below queries as well for more information

SELECT SPECIFIC_NAME,ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES

SELECT * FROM INFORMATION_SCHEMA.ROUTINES ORDER BY ROUTINE_TYPE

SELECT DISTINCT ROUTINE_TYPE FROM  INFORMATION_SCHEMA.ROUTINES

How to Know Internal Details of any DB using Query.

How to know the list of procedures present in your DB

Select * from sys.procedures

SELECT NAME from SYS.PROCEDURES 
where name like '%CUSTOMER%'
order by name

How to Know the internal details of a Procedure

sp_depends procedureName

How to Know the internal details of a Table

sp_depends TableName

Select List of Table Names from any DB.

SELECT NAME from SYS.Tables
order by name

Select List of triggers from any DB.

SELECT NAME from SYS.Triggers
order by name

Select List of Views from any DB.

SELECT NAME from SYS.Views
order by name

Select List of Sequences
from any DB.

SELECT NAME from SYS.Sequences
order by name

Select List of Types
from any DB.
SELECT NAME from SYS.Types
order by name

Select List of Assemblies
from any DB.
SELECT NAME from SYS.Assemblies
order by name

Select List of Functions
from any DB.
SELECT ROUTINE_NAME FROM information_schema.routines WHERE routine_type = 'function'

Brainstorming Question on OOP'S

1. what is the need of a Constructor in a class?
2. What is the use of the static keyword for Classes, Variables, and Methods?
3. What is the difference between static, readonly and Constant variable?

Looking for New Job .NET developer must understand

• SOLID Principals
• Design Patterns
• Memory Management(Garbage Collection & IDisposable)
• MVC over WebForms
• DotNet Core Framework
• No-SQL databases
• Boxing and Un-boxing
• Hoisting and Closures
• reference type and value type

Advantages of SOLID principal

Single Responsibility Principle
A class should have one, and only one, the reason to change

Benefits
More readable code
More robust
More maintainable

Smaller functions

Open Closed Principle
Software entities should be open for extension but closed for modification

Change to an entity cascades changes to dependent modules Fragile Unpredictable Modules should never change Extend behavior – add new code Don’t change the existing working code

Liskov Substitution Principle.
Derived classes must be substitutable for their base classes

Important for any program conforming to OCP Caller should not be surprised by substituting base type for subtype Virtual members must exist in derived classes Must do useful  expected workLiskov Substitution Principle

Useful work Must be implemented Expected work Should keep the behavior of the base class

Interface Segregation Principle
Make fine grained interfaces that are client specific

Deals with the disadvantage of “fat” interfaces
Clients forced to depend on interfaces they don’t use subjected to changes in that interface
MembershipProvider – forces 27 methods/properties

Benefits
Lower coupling – better maintainability
Higher cohesion – better robustness

Dependency Inversion Principle
Depend on abstractions, not concretions

Modules should not depend on modules implementing details
Depend on abstractions

Dependency Inversion principle

Dependency Inversion principle

Entities must depend on abstractions not on concretions. It states that the high level module must not depend on the low level module, but they should depend on abstractions.

Interface segregation principle

Interface segregation principle

A client should never be forced to implement an interface that it doesn't use or clients shouldn't be forced to depend on methods they do not use.

Liskov substitution principle

Liskov substitution principle

Let q(x) be a property provable about objects of x of type T. Then q(y) should be provable for objects y of type S where Sis a subtype of T.

Open-closed Principle

Open-closed Principle

Objects or entities should be open for extension, but closed for modification.

Single-responsibility Principle

Single-responsibility Principle

S.R.P for short - this principle states that:

A class should have one and only one reason to change, meaning that a class should have only one job.

Solid Principals

• S stands for SRP (Single responsibility principle).
• O stands for OCP (Open closed principle)
• L stands for LSP (Liskov substitution principle)
• I stand for ISP ( Interface segregation principle)
• D stands for the DIP ( Dependency inversion principle)

How to learn in a sequence. Then you will get the better understanding.

1.  Single responsibility principle

2.  Interface segregation principle

3. Open closed principle

4. Liskov substitution principle

5. Dependency inversion principle

 Single Responsibility Principle (SRP)

• Each class and module should focus on a single task at a time
• Everything in the class should be related to that single purpose
• There can be many members in the class as long as they related to the single responsibility
• With SRP, classes become smaller and cleaner
• The code is less fragile 

Interface Segregation Principle (ISP)

• The interface-segregation principle (ISP) states that "no client should be forced to depend on methods it does not use".
• This means, instead of one fat interface many small interfaces are preferred based on groups of methods with each one serving one sub-module.
• The ISP was first used and formulated by Robert C. Martin while consulting for Xerox. 

Open/Closed Principle

In object-oriented programming, the open/closed principle states that "software entities such as classes, modules, functions, etc. should be open for extension, but closed for modification" 

• Which means any new functionality should be implemented by adding new classes, attributes, and methods, instead of changing the current ones or existing ones.
• Bertrand Meyer is generally credited for having originated the term open/closed principle and This Principle is considered by Bob Martin as "the most important principle of object-oriented design".

Liskov Substitution Principle (LSP)

Substitutability is a principle in object-oriented programming and it states that, in a computer program, if S is a Subtype of T, then objects of type T may be replaced with objects of type S 

• Which means, Derived types must be completely substitutable for their base types
• More formally, the Liskov substitution principle (LSP) is a particular definition of a subtyping relation, called (strong) behavioral subtyping
• This Principle is introduced by Barbara Liskov in 1987 during her conference address on Data abstraction and hierarchy
• This principle is just an extension of the Open Close Principle

Dependency Inversion Principle (DIP)

• High-level modules should not depend on low-level modules. Both should depend on abstractions. 
• Abstractions should not depend on details. Details should depend on abstractions.