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

SQL Server – ASCII function with example

ASCII function ASCII will return ASCII value of left most character of the string or character expression. Syntax: SELECT ASCII(string or character_expression) It take input parameter as string or  single character value and returns integer as result. Example Here we will use ASCII function to find out the ASCII code value of Alphabet A, B ...

SQL Server – Disable and enable SA account

Received request to disable all sql server SA account from DEV, UAT and PROD, Can we modify permissions of SA account ? answer is not possible. We cant modify permissions of sql server SA account. SA account can be disable or enable using script or sql server management studio. How to disable or enable sql server SA ...

SQL Server 2008 – List of Character String function

Following string functions can be use only with string as input parameter and returns either string or numeric in output. 1. ASCII -> Returns ASCII value of leftmost character from string 2. CHAR -> Converts ASCII value to character 3. CHARINDEX -> Returns position of search expression or character from string 4. DIFFERENCE -> Returns ...