Things to know before going to join new project.

1. Get List of Stored Procedure In a Database  select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE=’PROCEDURE’ 2. Find All Number of  Functions  In a Database SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ‘FUNCTION’. 3. Number of Triggers In a Database. SELECT sysobjects.name AS trigger_name ,USER_NAME(sysobjects.uid) AS trigger_owner ,s.name AS table_schema ,OBJECT_NAME(parent_obj) AS table_name … More Things to know before going to join new project.

Delete repeated Ids from Table – Performance Improvement.

CREATE TABLE dbo.Data( FriendlyFunctionCode INT, MemberFirmId VARCHAR(100),FunctionLevel3Desc VARCHAR(100)) GO INSERT INTO dbo.Data VALUES(1,’Value1′,’Value2′) ,(1,’Value2′,’Value3′) ,(2,’Value4′,’Value5′) SELECT * FROM dbo.Data ;WITH CTE AS ( SELECT FriendlyFunctionCode,MemberFirmId, FunctionLevel3Desc, Row_number()OVER (PARTITION BY FriendlyFunctionCode ORDER BY (SELECT 1) ) AS Rn FROM dbo.Data) SELECT * FROM CTE WHERE Rn = 1 DROP TABLE Data

TSQL – How to Find duplicate records in a SQL Server Table.

CREATE TABLE dbo.Customer( ID INT, FirstName VARCHAR(100),LastName VARCHAR(100),Age INT) GO INSERT INTO dbo.Customer VALUES(1,’Rajesh’,’Yadav’,27) ,(1,’Rahul’,’sarode’,26) ,(2,’Raza’,’M’,32) ,(3,’Sukhjeet’,’Singh’,27) ,(4,’Sukhjeet’,’Singh’,28) ,(5,’Rahul’,’sarode’,26) SELECT * FROM dbo.Customer ;WITH CTE AS ( SELECT FirstName, LastName, Row_number() OVER ( PARTITION BY FirstName, LastName ORDER BY ( SELECT 1) ) AS Rn FROM dbo.Customer) SELECT * FROM CTE WHERE Rn > 1 … More TSQL – How to Find duplicate records in a SQL Server Table.

Puzzle On DATEADD Function

Why are the values so similar can any one explain me? Please execute the below Query and see the result SELECT DATEADD(MONTH, 1, ‘2015-01-28’) SELECT DATEADD(MONTH, 1, ‘2015-01-29’) SELECT DATEADD(MONTH, 1, ‘2015-01-30’) SELECT DATEADD(MONTH, 1, ‘2015-01-31’) Write your explanation in the comments.

How To Pass Optional Parameters in SQL Server Stored Procedure.

A common struggle is how to pass optional parameters to a stored procedure.Let’s talk about a simple example. You have an employees table that has the employee id, first name and last name. The table is defined as follows: create table [dbo].[employees] ( [id] int identity(1, 1) not null, [firstName] varchar(255) null, [lastName] varchar(255) null, … More How To Pass Optional Parameters in SQL Server Stored Procedure.

SQL SERVER – Puzzle .

CREATE TABLE #temp ( emp_name NVARCHAR(200) NOT NULL, emp_middlename NVARCHAR(200) NULL, emp_lastname NVARCHAR(200) NOT NULL ); INSERT INTO #temp VALUES( ‘SQL’, NULL, ‘Authority’ ); INSERT INTO #temp VALUES( ‘SQL’, ‘Server’, ‘Authority’ ); SELECT emp_name + emp_middlename + emp_lastname AS Result FROM #temp; DROP TABLE #temp; GO As you can see, the problem is getting the … More SQL SERVER – Puzzle .

SSIS – Create Text File Logging for SSIS Package Execution With DateTime.

Aim: We have created a SSIS Package. Now we want to enable Logging for our Package and want to create a log file ( Text File Logging) each time the package executes. The Log file name should look like “PackageName_Date_Time.log” Solution: SSIS provides different types of logging such as Text File Logging SQL Server Profiler … More SSIS – Create Text File Logging for SSIS Package Execution With DateTime.