Cannot schema bind view ‘ViewName’ because name ‘TableName’ is invalid for schema binding.

Are you trying to create a view with SCHEMABINDING Option and getting error like “Cannot schema bind view ‘ViewName’ because name ‘TableName’ is invalid for schema binding……” When we create view with SCHEMABINDING it is necessary to specify the schema name in objects name of SELECT statement otherwise it throw errors. For Example : 1 Read more about Cannot schema bind view ‘ViewName’ because name ‘TableName’ is invalid for schema binding.[…]

How To Compare Table Data And Detect Changes In SQL Server

Recently, I was working on a problem scenario where i need to maintain the histories of the table row data if any updates occurs to a row in the table. And histories maintenance should be maintained only if any data change is there while updating a table row. This post is demonstrate how the same Read more about How To Compare Table Data And Detect Changes In SQL Server[…]

How to get IDENTITY values on Multi-Rows insertion in SQL Server

In general problem scenario, we need to capture the IDENTITY value after insertion operation in a table. This is achieved by using SCOPE_IDENTITY(), @@IDENTITY and IDENT_CURRENT() function. The above functions doesn’t works in case of multi-rows insertion. SQL Server 2005 introduced the OUTPUT clause as part of INSERT statement which we can use to capture IDENTITY  values on multi-rows insertion. Problem Scenario Read more about How to get IDENTITY values on Multi-Rows insertion in SQL Server[…]

How To Import Data From Excel Sheet Into A SQL Server Database using C#

Recently I was working on project where I was given huge set of master data in a excel file which i had to migrate to the database. Generally I used to prefer some migration tools for such task like Talend but unfortunately the software was not installed on my system because of recent formatting of Read more about How To Import Data From Excel Sheet Into A SQL Server Database using C#[…]

How To Drop Multiple Databases In SQL Server

During development, Developer generally create a bunch of temporary databases that you probably do not want to keep in the database for long term. Most DBAs drop at a time using either a single DROP statement or SSMS. This post explains how we can drop multiple databases in a single query. To drop multiple database we Read more about How To Drop Multiple Databases In SQL Server[…]

How To Alter User-Defined Table Types in SQL Server

I cannot use ALTER to modify the type nor can I drop and recreate it since it is being referenced by existing Stored Procedures. That means, reference must be removed first. In this post, We will learn how to alter User-Defined Table types in SQL Server. Example Case: Let’s say we have multiple stored procedure Read more about How To Alter User-Defined Table Types in SQL Server[…]

How To Get Row Count For All Tables In SQL Server Database

Recently, I was working in analyzing a healthcare database where one simple task was to identify the big tables in terms of “Number of records”. So. I needed to get the row count for each tables in a SQL Server Database. There are many way to get this data. See the below T-SQL scripts: 1. Read more about How To Get Row Count For All Tables In SQL Server Database[…]

How to Track Stored Procedure, Function, View changes in SQL Server

Do you want to track the changes made on database objects like Stored Procedures, Views, Functions? SQL Server supports DDL Triggers that can be used to accomplish this task. SQL Server DDL Triggers is a special kind of trigger that fire in response to Data Definition Language (DDL) statements – CREATE, ALTER, DROP (Table, Function, Read more about How to Track Stored Procedure, Function, View changes in SQL Server[…]

How To Create And Schedule Job Using SQL Server Agent

In this post, we walk through the step by step process of creating and scheduling a job using SQL Server Management Studio.   What is SQL Server Agent ? SQL Server Agent is a component of Microsoft SQL Server which allows the database administrator (DBA) to schedule jobs and handles other automated tasks such as database backups Read more about How To Create And Schedule Job Using SQL Server Agent[…]

How to search a specific text in a SQL Server Stored Procedures, Functions, Views or Triggers

As a DB developer, I often needed to find a stored procedures, functions, views and triggers  that contains a specific piece of text string. For an example, say I want to find any Stored Procedures, Views or Functions that contains a text ‘codemog’. So, How to search a specific text in a SQL Server Stored Procedures,Views or functions Read more about How to search a specific text in a SQL Server Stored Procedures, Functions, Views or Triggers[…]