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

SQL Server – insufficient free space on disk volume while restoring database

Sometime you may come across situation where you try to restore database having large transaction log for small database. We also faced issue of insufficient free space on disk that was related to transaction log size while restoring production database on development server.    There is insufficient free space on disk volume ‘O:\’ to create the database. The ...

SQL Server – CHARINDEX Function with example

SQL Server CHARINDEX function returns starting position of a pattern you specify. CHARINDEX takes two parameter, first parameter is specify pattern to be search in expression. Syntax SELECT CHARINDEX(‘pattern’,’Expression or string’) 1. Pattern -> Value to be search in string or expression 2. Expression or string -> Specified pattern to be under second parameter value. Example ...

SQL SERVER – Difference Between CHARINDEX and PATINDEX with example

CHARINDEX or PATINDEX can be use to search specify pattern in table column in sql server. Difference between CHARINDEX and PATINDEX is one use wild card character to search pattern in sql server CHARINDEX and PATINDEX gives you the starting position of a pattern in STRING or Experssion. Example of CHARINDEX SELECT CHARINDEX(‘Hello’,’Hello World’) as ...

BCP Error – bcp is not recognized as an internal or external command

There are lots of options available in BCP command, you can see by typing “bcp /”? in the command prompt. C:\>bcp northwind.dbo.customers out c:\custdb.txt -T -c While executing BCP command if it gives error saying “’bcp’ is not recognized as an internal or external command,operable program or batch file. Then go to “C:\Program Files\Microsoft SQL ...

SQL Server – CHAR() – Convert ASCII code to Character with example

SQL Server – CHAR() It will convert ASCII code to Character value, takes integer value as input parameter. Syntax: SELECT CHAR(integer_value) Input parameter ->ASCII code as Integer_value ranges from 0 to 255, returns NULL value if it exceeds more then 255. Example Here we are passing input parameter value 65 to CHAR() function that will ...