How to get the name and definition of all Stored Procedures in a Database?


Today I wanted to get the script of all the Stored procedures in a database. I used this query to do so. Pretty much self explanatory is this, isn’t it?

Use [myDatabaseName]

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE=’PROCEDURE’

how to convert an integer to string and left pad it with zero’s?


Here’s a quick and simple way of converting an integer to string (varchar) and left pad it with leading zeros like 00012. This example will get the result in 5 digits

select right(‘00000’ + cast(yourfield as varchar(5)), 5)

How to make a case sensitive comparision of strings in SQL Server


String in SQL Server are by default case insensitive. That means that “SQL” is equivalent to “sql” and it causes problems when you’re making a password authentication mechanism which is supposed to be case senstive. Here’s an example stored procedure in which I’m making a case sensitive string comparison for the password field using Collations.

CREATE PROCEDURE [dbo].[proc_AuthenticateUser]
(
@UserName varchar(50),
@Password varchar(50)
)
AS
BEGIN

SET NOCOUNT ON
DECLARE @Err int

SELECT     dbo.[User].Id
FROM         dbo.[User]
WHERE     (dbo.[User].Password = @Password  COLLATE SQL_Latin1_General_CP1_CS_AS) AND (dbo.[User].Username = @UserName)
SET @Err = @@Error

RETURN @Err
END

How to change the ownership of a database


You can change the ownership of a database with this statement. It cured another problem that I got, I dont remember the exact statement

sp_changedbowner  @loginame =  ‘sa’ ,  @map= false

How to determine effective permission of an SQL Server 2005 object?


You can find out the effective permissions on an SQL Server 2005 object by using a function called fn_my_permissions. Here’s what’s written in the books online.

fn_my_permissions 

Returns a list of the permissions effectively granted to the principal on a securable.

Syntax

fn_my_permissions ( securable , 'securable_class')

Arguments

securable
Is the name of the securable. If the securable is the server or a database, this value should be set to NULL. securable is a scalar expression of type sysname. securable can be a multipart name.

securable_class
Is the name of the class of securable for which permissions are listed. securable_class is a sysname. securable_class must be one of the following: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, ENDPOINT, FULLTEXT CATALOG, LOGIN, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVER, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION.

 

I used it like this:-

USE myDatabase

Select * from fn_my_permissions (‘my_storedProc, ‘OBJECT’)

also remember that this is a table valued function so you can’t use the EXEC Statement with this

How to reseed the identity value of a table?


I was working on a project and there was a lookup table in that which needed to be populated and its identity values needed to strictly match with the database at my client’s end. At first when I tried to insert data from another table it turned in to an error, the rows didnt get inserted but the identity value increased due to a lost transaction. So I ran this script in order to reset the indentity value in that table. Please note that to start the identity values from 1 you need to reseed it to zero. The other important thing is that you should delete the rows with a higher identity value because it will not skip that, but it will try to overwrite it which would result in an error.

dbcc checkident (mytableName, reseed, 0)

How to access data from two different tables in SQL Server 2005?


howdy,

well I was having a problem where I needed to populate the table of my SQL Express 2005 database from another table in another database. So I wrote the query in the following fashion:

INSERT INTO debtdb.SchemaName.TableName (column1, column2….)

SELECT Column1, column2…

FROM dbName.SchemaName.TableName

 

my sample query was this

Insert into db2.dbo.tblNationality(NationalityName) 

Select

Name FROM db1.dbo.tblNationality

what it does is that it takes the data from the database called db1 and inserts it into another database –on the same server, called ‘db2’.