Code Simplified – Viral Sarvaiya

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

Posts Tagged ‘T SQL’

« Previous Entries

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 Data of All Tables of database in Sql Server

Posted by Viral Sarvaiya on September 18, 2012


When I am searching particular text from Stored Procedure in sql server as I wrote in my last blog post http://codesimplified.com/2012/09/10/search-particular-text-used-in-stored-procedure-in-sql-server/  I get strike that is there any way to search any particular text which is used in data of the all table in database?

Here is the SP which return the list of the table name with column name and whole text if that text is in between the big text.

/****** Object:  StoredProcedure [dbo].[SearchTextFromAllTables]    Script Date: 09/10/2012 15:00:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
	-- Purpose: To search all columns of all tables for a given search string
	-- Written by: Narayana Vyas Kondreddi
	-- Site: http://vyaskn.tripod.com
	-- Tested on: SQL Server 7.0, SQL Server 2000, SQL server 2005
	-- Date modified: 28th July 2002 22:50 GMT

CREATE PROCEDURE [dbo].[SearchTextFromAllTables]
(
@StrSearch nvarchar(100)
)
AS
BEGIN

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @StrSearch2 nvarchar(110)
SET  @TableName = ''
SET @StrSearch2 = QUOTENAME('%' + @StrSearch + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM     INFORMATION_SCHEMA.TABLES
WHERE         TABLE_TYPE = 'BASE TABLE'
AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND    OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM     INFORMATION_SCHEMA.COLUMNS
WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
AND    TABLE_NAME    = PARSENAME(@TableName, 1)
AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND    QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @StrSearch2
)
END
END
END

SELECT distinct ColumnName,ColumnValue FROM #Results
END

for run this query


exec SearchTextFromAllTables 'TextValue'

You will find the result as below.

Hope this will helps you..

Thanks.

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Posted in Sql Server | Tagged: , , , , , , , , , , , , , , | Leave a 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 »

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 »

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: , , , , | 3 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 »

Remove duplicate records from the database table

Posted by Viral Sarvaiya on October 18, 2010


In my recent post http://viralsarvaiya.wordpress.com/2010/09/27/removing-duplicate-records-from-datasetdatatable/, I explain how to remove duplicate records form the dataset or datatable.

But if you want to delete duplicate records directly from the database then…..
First find the duplicate records,


SELECT MAX(ID) as ID, Name, Company FROM Employee GROUP BY Name, Company HAVING COUNT(*) > 1

This records give me the total duplicate records

Now to delete the duplicate records,


DELETE FROM Employee WHERE ID IN (SELECT MAX(ID) FROM Employee GROUP BY Name, Company HAVING COUNT(*) > 1)

Enjoy Coding…..

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

Calculating Work-Days in T-SQL

Posted by Viral Sarvaiya on May 3, 2010


Overview

One of the more serious questions in project scheduling, employee planning, and accounting of time is “How many workdays are there?” In other words, given a start date and an end date, how many weekdays (usually a “workday”) are there in the date range including the start date and the end date?

The normal response I get to that question varies but has been limited to “It can’t be done in SQL”, “Ya gotta use a CURSOR”, or “You have to build a table”. This article will take you through all of the logic behind building a nasty fast “Work Day Calculator” function, the crux of which is a single SELECT statement. A fully commented production-worthy UDF is available at the end of this article.

For more details :

http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/

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

Date Manipulation with DATEADD/DATEDIFF

Posted by Viral Sarvaiya on April 7, 2010


Many times when working with date types in SQL we need to remove the time portion and leave just the date. There are a number of ways to accomplish this, but this article will focus on my favorite, the DATEADD/DATEDIFF

for more details click here

http://www.sqlservercentral.com/articles/Date+Manipulation/69694/

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

« Previous Entries
 
Follow

Get every new post delivered to your Inbox.

Join 44 other followers

%d bloggers like this: