SQL Server – DROP table with Foreign key constraints – Example

SQL Server will not allow you to drop table without dropping foreign key constraint, foreign key means column of one table refers primary key of another table. This relationship called a Foreign key relationship, here primary table will act as parent table and secondary table called as child table. If you try to drop primary table (Parent ...

SQL Server – Fix – Orphan login Error using sp_change_users_login system stored procedure

As SQL DBA, we often deal with orphan user related issues while moving database from one server to another. We got an email from client saying they are unable to connect database server after database movement … Login Eror Message was “Login failed for user ‘idb_dev’.(Microsoft SQL Server, Error: 18456)”.  also Error 15023: User already exists in ...

SQL Server Certificate to grant permissions on database

Grant permission using sql server certificate to execute system stored procedure like sp_updatestats. Sql server certificate is helpful to provide access to different users to execute certain stored procedure which require exclusive privilege after valid authentication method. Example 1. Create a stored procedure to execute sp_updatestats USE [master] GO CREATE PROCEDURE [dbo].[sp_updatestats_user] AS BEGIN exec ...

SQL Server – DBCC SQLPERF to monitor transaction log space

To keep track on database transaction log growth, you can use DBCC SQLPERF command that monitors transaction log space used in the database. Log file size is depends on recovery model of database and backup job.  Frequently running transaction log backup can keep transaction log in good shape. Sql server has dbcc sqlperf command to track ...

SQL Server – Sp_msforeachtable undocumented stored procedure

Sp_msforeachtable SQL Server has sp_msforeachtable as undocumented stored procedure that can be used to run query against all table in single database. Lets say you want to get the record count from each table so in that case you can use below command to generate the list of tables as well as record count. Example ...

SQL Server – sp_msforeachdb undocumented stored procedure with simple example

Sp_msforeachdb can be handy to use as option to perform maintenance task for each database in sql server. sp_msforeachdb is undocumented stored procedure and not supported by Microsoft.  Sp_msforeachdb will help you to iterate through all database and execute your sql command.  Sp_msforeachdb  may be discontinued in future version of sql server. DBA use is ...

SQL Server – Difference between LEFT and RIGHT function with example

  SQL Server LEFT and RIGHT function fetches number character from expression, LEFT function use to fetch LEFT side value whereas RIGHT function gives value from RIGHT side of expression. LEFT vs RIGHT function in sql server SQL Server LEFT  function returns character and Takes two parameter, first parameter is string value or any expression and ...

SQL Server – Difference and SOUNDEX function with example

Difference function returns integer value as output, value ranges between 0 to 4. Lower number indicates less chance of matching or no match and highest number 4 indicates exact match. Difference function is based on SOUNDEX function in sql server which returns alphanumeric value. Lets go through simple examples of DIFFERENCE function and SOUNDEX function. 1. ...

SQL Server – System databases – Master,MSDB,Model,Resource and tempdb

System databases are default database after sql server installation, master,msdb,model, resource and tempdb are part of system database. All sql server information are stored in system database. using system catalog or system table you can read sql server internal information. System Database master, model, msdb, resource and tempdb database. Master database in sql server Master ...

SQL Server 2008 R2 – Copy database schema objects – Generate script for all tables, contraints, functions, stored procedure and triggers

As DBA, Copying database objects like tables, stored procedure, views, triggers either on UAT or DEV. Schema refresh is another example of copying all database objects to another instance. In sql server 2008 and r2, you can use generate script wizard to script out entire database object. Follow the steps below to script out entire ...