Structured Query Language: structured query language sql

To make SQL Queries Case Sensitive

To make SQL Queries Case Sensitive using Collation.You can make SQL Queries Case sensitive at the column level or Table level or DataBase level.

Introduction

How to make SQL queries case sensitive.

How to do it?

If you installed SQL Server with the default collation options, you might find that the following queries return the same results:

CREATE TABLE mytable 
( 
mycolumn VARCHAR(10) 
) 
GO 
SET NOCOUNT ON 
INSERT mytable VALUES('Case') 
GO 
SELECT mycolumn FROM mytable WHERE
mycolumn='Case' 
SELECT mycolumn FROM mytable WHERE
mycolumn='caSE' 
SELECT mycolumn FROM
mytable WHERE mycolumn='case'

You can alter your query by forcing collation at the column level:

SELECT mycolumn FROM mytable WHERE 
mycolumn = 'case' AND 
CAST(mycolumn AS VARBINARY(10)) = CAST('Case' AS VARBINARY(10)) 
SELECT mycolumn FROM mytable WHERE 
mycolumn = 'case' AND 
CAST(mycolumn AS VARBINARY(10)) = CAST('caSE' AS VARBINARY(10)) 
SELECT mycolumn FROM mytable WHERE 
mycolumn = 'case' AND 
CAST(mycolumn AS VARBINARY(10)) = CAST('case' AS VARBINARY(10)) 
-- if myColumn has an index, you will likely benefit by adding 
-- AND myColumn = 'case'

If you want to do this in a more global way, instead of modifying each individual query, you can force the collation at the database level, or at the column level, using the ALTER DATABASE and ALTER TABLE commands, respectively. You can see the current collation level on the properties tab of the database server, through Enterprise Manager (if you're going to change this setting, MAKE NOTE OF THIS VALUE):

And you can see the description from running the following query:

SELECT DATABASEPROPERTYEX('', 'Collation')

As changing this setting can impact applications and SQL queries, I
would isolate this test first. In SQL Server 2000, you can easily run
an ALTER TABLE statement to change the sort order of a specific column,
forcing it to be case sensitive. First, execute the following query to
determine what you need to change it back to:

EXEC sp_help
'mytable'

The second recordset should contain the following information,

in a default scenario:

 Column_Name Collation 

----------- ---------------------------------------------- 

mycolumn    SQL_Latin1_General_CP1_CI_AS

Whatever the 'Collation' column returns, you now know what you

need to change it back to after you make the following change,

which will forcecase sensitivity:

ALTER TABLE mytable
ALTER COLUMN mycolumn
VARCHAR(10)
COLLATE
Latin1_General_CS_AS
GO SELECT mycolumn FROM mytable WHERE
mycolumn='Case'
SELECT mycolumn FROM mytable WHERE
mycolumn='caSE'
SELECT mycolumn FROM
mytable WHERE mycolumn='case'
If this screws things up, you can change it back, simply by issuing a new ALTER TABLE statement (be sure to replace my COLLATE identifier with the one you found previously):

ALTER TABLE mytable 
ALTER COLUMN mycolumn VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS

Conclusion

This way to make sql querie Case Sensitive

Getting all stored procedures from a database

Here how you can get all the stored procedure from a database. Hey not only the name but the stored procedures definitions also.

Get by using Cursor

declare @proc_name varchar(100)
declare @str_query nvarchar(MAX)

declare loop_proc cursor
for select [name] from sys.procedures where type='P'
and is_ms_shipped = 0
open loop_proc

FETCH NEXT FROM loop_proc INTO @proc_name
IF @@FETCH_STATUS <> 0
PRINT ' <>'
WHILE @@FETCH_STATUS = 0

BEGIN
SELECT @str_query = 'sp_helptext ' + @proc_name
PRINT @str_query
exec sp_executesql @str_query
FETCH NEXT FROM loop_proc INTO @proc_name
END
CLOSE loop_proc
DEALLOCATE loop_proc

Run these sql statements and view the result in Text mode. You will get all scripts for stored procedures.

Get from sysobjects

This is better option to get all stroed procedures from a database.

SELECT [text]
FROM
sysobjects o
JOIN syscomments c ON o.id = c.id
WHERE xtype = 'P'

Difference between TOP clause in SQL 2000 and SQL 2005

Generally TOP clause is used to perform SELECT on top n results. This feature of TOP is extended in SQL 2005 so that we can also use expression apart from int, bigint and percent to perform query and also extended to be used in UPDATE and DELETE statements.

Introduction

Generally TOP clause is used to perform SELECT on top n results. This feature of TOP is extended in SQL 2005 so that we can also use expression apart from int, bigint and percent to perform query and also extended to be used in UPDATE, and DELETE statements.

In SQL 2000

syntax: select Top N [Percent]

EX:

select Top 10 * from TableName
or
select Top 10 Percent * from TableName

n specifies how many rows are returned. If PERCENT is not specified, n is the number of rows to return. If PERCENT is specified, n is the percentage of the result set rows to return

Drawbacks:

1) We could not parameterize.
2) It will work only for select statements.

If we want to restrict the number of rows affected by a select at runtime, or restrict the rows affected by an update or delete you had to explicitly describe the rows using a join or where clause, or you could cheat a bit by using ROWCOUNT, like this

set rowcount 10
delete from table where payratefieldname=1
set rowcount 0

It will work but the risk is if for some reason rowcount is not set to 0 then the other statements will also restricted to 10.

All these drawbacks are overcome in SQL 2005 by introducing Expression in syntax.

In SQL 2005

syntax: select Top (Expression) [Percent]

EX:

Is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

Ex:

select Top 10 * from TableName
or
select Top 10 Percent * from TableName
or we can set at runtime as

Declare @ int
SET @topNum = 10
select TOP (@topNum) * from

For select statements you must specify parentheses if you are passing a parameter, otherwise they are optional

Select Top (@topNum) * from TableName

Select Top 10 * from TableName

When doing an update or delete, you have to use the parentheses in both cases:

Delete Top (@topNum) from employeesDelete Top (10) from TableName

update Top (@topNum) TableName set fieldname = @fieldvalue

update Top (10) from employees set fieldname = @fieldvalue