fnsearchstring

select * from fnsearchstring(‘%customer%’)

How to delete a stored procedure

SELECT name as stored_procedure_name
,SCHEMA_NAME(schema_id) as schema_name
,type_desc
,create_date
,modify_date
FROM sys.procedures;

DROP PROCEDURE <stored_procedure_name >;
GO

Simple Stored Procedure

USE MASTER_DB;
GO
CREATE PROCEDURE SP_GetEmployees_Details
@ENAME nvarchar(50),
@JOB nvarchar(50)

AS

SET NOCOUNT ON;
SELECT e.ENAME, e.JOB, d.DNAME,e.SAL,*
FROM EMP e,DEPT d
WHERE e.DEPTNO=d.DEPTNO
AND e.ENAME = @ENAME AND e.JOB = @JOB
AND e.COMM IS NULL;
GO

EXECUTE SP_GetEmployees_Details N’BLAKE’, N’MANAGER’;
— Or
EXEC SP_GetEmployees_Details @ENAME = N’BLAKE’, @JOB = N’MANAGER’;
GO
— Or
EXECUTE SP_GetEmployees_Details @ENAME = N’BLAKE’, @JOB = N’MANAGER’;
GO

Database ‘MASTER_DB’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details

Msg 945, Level 14, State 2, Line 1
Database ‘MASTER_DB’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Capture

Recovery Pending.

Untitled

Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it.

Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it.

Alter account & unlock

Execute the below SQL code in master database:

ALTER LOGIN sa WITH PASSWORD=’password’ UNLOCK

How to change the column data type to Identity?

How to change the data type int to identity (1,1)? 

When we set identity to any column no need to insert the value. System will automatically inserts the sequential number. It is not possible to change the data type to identity for tables which have large amount of data. Here is the way: Run the below query to create Temp table with the same data structure.  Select * into Temp_table from Original_Table where 1=2;  Right click on the table Select the column Go to the properties Select ‘Is Identity’ option to ‘Yes’ Save.  Now execute the below SQL statement: Insert into Temp_Table select * from Original_table  Now all the data loaded into Temp_Table. Now drop the Original_Table Rename the  Temp_Table to Original_Table. Create keys if existed on Original_Table.How to change the data type int to identity (1,1)? When we set identity to any column no need to insert the value. System will automatically inserts the sequential number. It is not possible to change the data type to identity for tables which have large amount of data. Here is the way: Run the below query to create Temp table with the same data structure.  Select * into Temp_table from Original_Table where 1=2;  Right click on the table Select the column Go to the properties Select ‘Is Identity’ option to ‘Yes’ Save.  Now execute the below SQL statement: Insert into Temp_Table select * from Original_table  Now all the data loaded into Temp_Table. Now drop the Original_Table Rename the  Temp_Table to Original_Table. Create keys if existed on Original_Table.

Mismatch between the PK and FK constraints in database tables

We can see the below error due to mismatch between the PK and FK constraints in database tables.

An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “The statement has been terminated.”.
An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “The INSERT statement conflicted with the FOREIGN KEY constraint “<Dimension Table>_FK1”. The conflict occurred in database “<Database Name>”, table “<Dimension Column>”, column ‘<Primary Key Column>’.”.

The below script is useful to find those mismatches:

Query to no of rows affected by FK constraints:
select
SCHEMA_NAME(b.schema_id) as SchemaName,
b.name as TableName,
SCHEMA_NAME(b1.schema_id) as FKSchemaName,
b1.name as FKTableName,
a.name as FKName,
c.rows as Rows,
c1.rows as FKRows
from sys.foreign_keys a
inner join sys.objects b on a.parent_object_id = b.object_id
inner join sys.objects b1 on a.referenced_object_id = b1.object_id
inner join sys.partitions c on b.object_id = c.object_id
inner join sys.partitions c1 on a.referenced_object_id = c1.object_id
where c.index_id in (0,1) and c1.index_id in (0,1)

Script to find the key constraints violation:

DBCC CHECKCONSTRAINTS (TableName) : Will checks an individual table
DBCC CHECKCONSTRAINTS (ConstraintName) : Will checks an individual constraint
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS : Will checks all constraints in the database
DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS : Will returns all rows that violate constraints
DBCC CHECKCONSTRAINTS WITH NO_INFOMSGS -: Will suppress messages when query runs