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?
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
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)
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]
SET NOCOUNT ON
DECLARE @Err int
WHERE (dbo.[User].Password = @Password COLLATE SQL_Latin1_General_CP1_CS_AS) AND (dbo.[User].Username = @UserName)
SET @Err = @@Error
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
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.
Returns a list of the permissions effectively granted to the principal on a securable.
fn_my_permissions ( securable , 'securable_class')
- 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.
- 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:-
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
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)
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…
my sample query was this
Insert into db2.dbo.tblNationality(NationalityName)
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’.