Archive for the ‘Sql Server’ Category
Blogs that are related to the sql server or database.
Posted by Viral Sarvaiya on December 14, 2012
In sql server we use one query for drop table/procedure/trigger like below
Drop table table_name
Drop procedure Procedure_name
Drop trigger Trigger_name
But if we want to delete some or all Tables/Procedure/Trigger then what?
have to delete one by one?
We can create one loop with cursor to drop table/procedure/trigger for that as like below.
we can create SP to delete perticulers of the database as passing text and type as a parameter.
For that we can use sys.objects table
For table Type = ‘U’
For procedure Type = ‘P’
For Trigger Type = ‘TR’
For more type of sys.objects please refer http://msdn.microsoft.com/en-us/library/ms190324.aspx
Stored procedure is as below.
ALTER PROCEDURE DeleteEntities
@Type VARCHAR(2),
@SearchText VARCHAR(50)
AS
BEGIN
DECLARE @procName varchar(500)
DECLARE @StrDrop VARCHAR(50)
IF (@type != '')
BEGIN
DECLARE cur cursor
FOR SELECT [name] FROM sys.objects WHERE TYPE = @Type AND NAME LIKE '%'+ @SearchText +'%'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
BEGIN
SET @StrDrop = ((CASE WHEN @Type = 'U' THEN 'drop table '
WHEN @Type = 'P' THEN 'drop Procedure '
WHEN @Type = 'TR' THEN 'Drop trigger '
ELSE ''
END) + @procName)
IF (@StrDrop != '')
BEGIN
EXEC @StrDrop
END
fetch next from cur into @procName
end
close cur
deallocate cur
END
END
As like this we can also drop table constraints.
Hope this will helps you.
Thanks
Like this:
Like Loading...
Posted in Sql Server | Tagged: Delete multiple Procedure form sqlserver, Drop all procedure, Drop all trigger, drop procedure, Drop table, drop trigger, sys.objects | Leave a Comment »
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.
Like this:
Like Loading...
Posted in Sql Server | Tagged: Find Foreign Keys, Find Foreign Keys in Sql Server, Foreign Keys, SQL, Sql Server, sys.columns, sys.foreign_key_columns, T SQL, Transact SQL T-SQL | 1 Comment »
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
Like this:
Like Loading...
Posted in Sql Server | Tagged: column name, CREATE PROCEDURE, CREATE TABLE, INFORMATION_SCHEMA, INFORMATION_SCHEMA.COLUMNS, INFORMATION_SCHEMA.TABLES, language sql, object id, Pl/Sql, QUOTENAME, Search Particular Text used in Data of All Tables of database in Sql Server, Sql Server, Stored procedure, T SQL, table schema | Leave a Comment »
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.
Like this:
Like Loading...
Posted in Sql Server | Tagged: CREATE PROCEDURE, INFORMATION_SCHEMA.ROUTINES, Pl/Sql, Search Particular Text used in Stored Procedure in Sql Server, SQL, Sql Server, Stored procedure, sys.procedures, sys.Tables, syscomments, sysobjects, T SQL | 1 Comment »
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
Like this:
Like Loading...
Posted in Sql Server | Tagged: ROWCOUNT, SET ROWCOUNT, SQL, SQL Query, Sql Server, Transact SQL T-SQL | Leave a Comment »
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.
Like this:
Like Loading...
Posted in Sql Server | Tagged: Bug in IsNumeric(), IsNumeric(), SQL, T SQL, What is wrong with IsNumeric()? | 3 Comments »
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..
Like this:
Like Loading...
Posted in Sql Server | Tagged: Pl/Sql, SPACE(), SQL, Sql Server, T SQL | Leave a Comment »
Posted by Viral Sarvaiya on January 16, 2012
Today i upload all report of my project and get error
Error : There was an Exception running the extension specified in the config file –> maximum request length exceeded.
The basic problem here, is that your posting an amount of data to a web app larger than it is configured to accept. Hence, it is throwing an error, and simply saying “no!”
It’s an easy fix though! You’ve got to tweak the web.config for the web app, which in the case of reporting server, is usually somewhere like this:
C:\Program Files\Microsoft SQL Server\MSRS10_50.SQLEXPRESS\Reporting Services\ReportServer
Find the web.config file for your reporting services instance, open it up, and track down the line that looks something like this
<httpRuntime executionTimeout = "9000" />
Now just add a max request length attribute in there to fix the problem, adjust your size as needed. This is 5meg.
<httpRuntime executionTimeout = "9000" maxRequestLength="500000" />
And now you’ll need to restart IIS. start->run->”iisreset”
Enjoy….
Like this:
Like Loading...
Posted in Report | Tagged: executionTimeout, httpRuntime, maximum request length exceeded, maxRequestLength, Report Server, Reporting Services, SQL EXPRESS, SQL Reporting Services Error, Sql Server | 1 Comment »
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…
Like this:
Like Loading...
Posted in Sql Server | Tagged: Identity, RESEED, Reset, SQL, SQL Index, SQL Performance, SQL Query, SQL Scripts, SQL Security, Sql Server, SQL Tips and Tricks, T SQL | Leave a Comment »
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…
Like this:
Like Loading...
Posted in Sql Server | Tagged: SQL, Sql Server, Trigger, Trigger in sql server, Ways to check whether a trigger exists in SQL Server | 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
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.
Run this query as like below
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.
Share this:
Like this:
Posted in Sql Server | Tagged: CREATE PROCEDURE, INFORMATION_SCHEMA.ROUTINES, Pl/Sql, Search Particular Text used in Stored Procedure in Sql Server, SQL, Sql Server, Stored procedure, sys.procedures, sys.Tables, syscomments, sysobjects, T SQL | 1 Comment »