Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Posts Tagged ‘SQL’

Find Foreign Keys in Sql Server

Posted by Viral Sarvaiya on October 12, 2012


Hi,

Here is the query to find list of foreign keys of the particular database,

Here this query gives name of foreign keys, table name, column name, referenced table name and referenced column name.


;With CTE As
(
Select Object_Name(constraint_object_id) Constraint_Name, Object_Name(parent_object_id) Table_Name, C.name Column_Name
From sys.foreign_key_columns FK
Inner Join sys.columns C On FK.parent_object_id = C.object_id And FK.parent_column_id = C.column_id
)
SELECT C.Constraint_Name,C.Table_Name,C.Column_Name, Object_Name(FK.referenced_object_id) Referenced_Table_Name,SC.name Referenced_Column_Name
from CTE C
Inner Join sys.foreign_key_columns FK On C.Constraint_Name = Object_Name(FK.constraint_object_id)
Inner Join sys.columns SC On FK.referenced_object_id = SC.object_id And FK.referenced_column_id = SC.column_id

Thanks.

Posted in Sql Server | Tagged: , , , , , , , , | 1 Comment »

Search Particular Text used in Stored Procedure in Sql Server

Posted by Viral Sarvaiya on September 10, 2012


Few days ago I get very tedious job in database,
I have to search all stored procedure which have use one particular table.

I get following 1 option of query from sys table of sqlserver

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%TBL_NAME%'    AND ROUTINE_TYPE='PROCEDURE'
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TBL_NAME%'

but this query only table table’s name, if We want particular string not table then this query will fail so below stored procedure will work.

CREATE PROCEDURE [dbo].[sp_search_code]
(
@SearchStr  varchar(100),
@RowsReturned int = NULL OUT
)
AS
BEGIN
SET NOCOUNT ON

SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',
CASE
WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
THEN 'Replication stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
THEN 'Extended stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
THEN 'Stored Procedure'
WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
THEN 'Trigger'
WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
THEN 'Table-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
THEN 'Scalar-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
THEN 'Inline function'
END AS 'Object type',

'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) + '''' AS 'Run this command to see the object text'

FROM syscomments c
INNER JOIN
sysobjects o
ON c.id = o.id
WHERE c.text LIKE '%' + @SearchStr + '%' AND
encrypted = 0    AND
(
OBJECTPROPERTY(c.id, 'IsReplProc') = 1  OR
OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsProcedure') = 1  OR
OBJECTPROPERTY(c.id, 'IsTrigger') = 1  OR
OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
)

ORDER BY 'Object type', 'Object name'

SET @RowsReturned = @@ROWCOUNT
END

Run this query as like below

exec sp_search_code 'SearchText'

You will get require output of list of stored procedure name.

In this Stored Procedure i take one output parameter for check number of rows return by Stored Procedure.

Hope this will help you.

Thanks.

Posted in Sql Server | Tagged: , , , , , , , , , , , | 1 Comment »

Difference Between LINQ to SQL and Entity Framework

Posted by Viral Sarvaiya on August 14, 2012


In my current company we have discussion about what is the difference between LINQ to SQL Vs Entity Framework and I get following conclusion from the surfing from internet (off course from google. 🙂 )

LINQ to SQL supports one to one mapping of database table or views or procedures or functions
Entity Framework map single class to multiple tables of database. Means you can map one table to multiple entities or multiple table to one entities.

LINQ to SQL does not support the creation of complex types
Entity Framework support the creation of complex types.

LINQ to SQL is easy to use
Entity framework is more complex compared to LINQ to SQL.

While introduction of LINQ to SQL it supports only sql server and letter on “DBLINQ” that can use with mysql, sqllite or other DBs
Entity Framework  plug with any database server like DB2, Sybase, Oracle, SQL Azure and other.

In LINQ to SQL, inheritance is difficult.
Entity Framework is simple to apply because it supports Table per class and table per Type.

LINQ to SQL file type is DBML
Entity Framework File type is EDMX.

LINQ to SQL has DataContext object which we can query the database,
Entity Framework, we can query database using LINQ To Entities through the ObjectContext object and ESQL(provides SQL like query language). In addition, Entity Framework has ObjectQuery class(used with Object Services for dynamically constructing queries at runtime) and EntityClient provider(runs query against conceptual model).

LINQ to SQL is slow for the first time run, after first run acceptable performance.
Entity Framework is also slow for the first but  performance is good then LINQ to SQL after first run.

LINQ to SQL has not capability to generate database from Model
Entity Framework has capability to generate database from Model.

Thanks.

Posted in .Net, ASP.NET, asp.net feature, LINQ | Tagged: , , , , , | Leave a Comment »

Random in Linq

Posted by Viral Sarvaiya on July 12, 2012


In sql query if we want to get one random row form the whole set of data we get from NEWID() in select statement’s order by clouse as like below.

Select top 1 * from Customers where IsActive = 1 order by newid()

What if we are using ORM like linq?

We can do this at the database, by using a fake UDF; in a partial class, add a method to the data context

partial class MyDataContext {
    [Function(Name="NEWID", IsComposable=true)]
    public Guid Random()
    { // to prove not used by our C# code...
        throw new NotImplementedException();
    }
 }

Then just order by ctx.Random(); this will do a random ordering at the SQL-Server courtesy of NEWID(). i.e.

var cust = (from row in ctx.Customers
   .where row.IsActive // your filter
   .orderby ctx.Random()
   select row).FirstOrDefault();

Note that this is only suitable for small-to-mid-size tables; for huge tables, it will have a performance impact at the server, and it will be more efficient to find the number of rows (Count), then pick one at random (Skip/First).

var qry = from row in ctx.Customers select row;

int count = qry.Count(); // 1st round-trip
int index = new Random().Next(count);

Customer cust = qry.Skip(index).FirstOrDefault(); // 2nd round-trip

We can also do woth Guid class

var cust = ctx.Customers
 .Where(x => x.IsActive)
 .OrderBy(x => Guid.NewGuid())
 .FirstOrDefault();

Hope this will helps you.

Thank you.

Posted in .Net, ASP.NET, LINQ | Tagged: , , , , , , , | Leave a Comment »

ROWCOUNT in Sql server

Posted by Viral Sarvaiya on June 6, 2012


Causes SQL Server to stop processing the query after the specified number of rows are returned.

SET ROWCOUNT 10;
 SELECT * FROM Employee;

If Employee table has 100 reocrds, query return only 10 records. SET ROWCOUNT works as a TOP.

Syntex:
SET ROWCOUNT { number | @number_var }

where “number | @number_var” is Integer value rows to be processed before stopping the specific query.

To set this option off so that all rows are returned, specify SET ROWCOUNT 0.

SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.

The setting of SET ROWCOUNT is set at execute or run time and not at parse time.

SET ROWCOUNT is not affect DELETE, INSERT and UPDATE statement so for that similar behavior use TOP in select Statement.

http://msdn.microsoft.com/en-us/library/ms188774.aspx

Posted in Sql Server | Tagged: , , , , , | Leave a Comment »

What is wrong with IsNumeric()?

Posted by Viral Sarvaiya on May 18, 2012


Today I face very strange thing with IsNumeric() function in sql

select IsNumeric('1234D234')
select IsNumeric('123A123')

Both query have alphanumeric value and both give different answer. very strange.

After a long time spent on google i get solution. We can say or cannot say that there are bug in IsNumeric() function.

IsNumeric() can falsely interpret non-numeric letters and symbols (such as D, E, and £, $), and even tabs (CHAR(9)) as numeric.

With this character, Why IsNumeric() can not give right answer? because D stand for Decimal. that value consider as a decimal value, E stand for exponential and $,£ is stand for currency symbol. so Isnumeric() fail to convert those values which has like D,E,$,£ characters.

so to get around this problem, we can create user define function which find the value is integer/numeric. as below.

CREATE FUNCTION dbo.CheckNumeric
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))

DECLARE @pos TINYINT

SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))

RETURN CASE
WHEN PATINDEX('%[^0-9.-]%', @num) = 0
AND @num NOT IN ('.', '-', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
AND
(
((@pos = LEN(@num)+1)
OR @pos = CHARINDEX('.', @num))
)
THEN
1
ELSE
0
END
END
GO

Or

CREATE FUNCTION dbo.CheckInteger
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))

RETURN CASE
WHEN PATINDEX('%[^0-9-]%', @num) = 0
AND CHARINDEX('-', @num) <= 1
AND @num NOT IN ('.', '-', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
THEN
1
ELSE
0
END
END
GO

Learn From: http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
Thanks.

Posted in Sql Server | Tagged: , , , , | 4 Comments »

SPACE() function of SQL Server

Posted by Viral Sarvaiya on February 16, 2012


Usually when we concatenate two fields like firstname and lastname we use ‘ ‘ between them like below

select firstname + ' ' + lastname from tblEmployee

And if we want more space then we use long space between them, but sql server provide function for print space between query.

Syntax:
SPACE(NO)

For example

select firstname + SPACE(2) + lastname from tblEmployee

It will return like below.

Viral  Sarvaiya

Thanks.

Enjoy..

Posted in Sql Server | Tagged: , , , , | Leave a Comment »

RESEED/Reset Table Identity Value in sql server

Posted by Viral Sarvaiya on January 6, 2012


DBCC CHECKIDENT can reset the identity value of the table,

For example if table has 500 reords and not we want to delete all records or want to start data from 600 then this command will help us.

DBCC CHECKIDENT (Table_name, reseed, 0)

OR

DBCC CHECKIDENT (Table_name, reseed, 600)

Most of our identity we start with 1 so must have to take care that identity set to 0.

If identity is set below values that currently are in table, it will violate the uniqueness constraint as soon as the values start to duplicate and will generate error.

Enjoy…

Posted in Sql Server | Tagged: , , , , , , , , , , , | Leave a Comment »

Ways to check whether a trigger exists in SQL Server

Posted by Viral Sarvaiya on December 13, 2011


Hi,

How to find there are trigger is exists in database or not?

there are 2 ways to find the list of the trigger in database


select * from dbo.sysobjects
where OBJECTPROPERTY(id, 'IsTrigger') = 1

or


select * from sys.triggers

and if you want to find trigger from particular table,


exec sp_helptrigger 'TableName'

or


select * from sys.triggers where name = 'TableName'

 

Enjoy…

Posted in Sql Server | Tagged: , , , , | Leave a Comment »

Difference between count(*) and count(column_name)

Posted by Viral Sarvaiya on September 13, 2010


what is count(*), count(column_name) and count(distinct column_name) ?

suppose we have employee table and having 100 records.

you all know what is count(*) in sql, this all function return same result if there is no record null in column.

select count(*) from employee
select count(emp_name) from employee
select count(distinct emp_name) from employee

that return the total employee from the employee table,

answer of this 3 query is 100.

but what happen if some records approx 20 of the emp_name is null?

now this 3 query give same answer?

no.

select count(*) from employee

return 100

select count(emp_name) from employee

return 80

select count(distinct emp_name) from employee

return 80

count(*) count the null records while count(column_name) omits the null value so it return 80(100 – 20) record.

hope you are clear with the count(*) count(column_name) and distinct column_name).

thank you.

Posted in Sql Server | Tagged: , , , , , | 1 Comment »

 
%d bloggers like this: