Here is some practice tips which you can remember and apply when writing SPs
DECLARE @starttime datetime SELECT @starttime = GETDATE() … Your code here … SELECT DATEDIFF(ms, @starttime, GETDATE()) AS 'Total Time (ms)'
CREATE PROC usp_subTable as select * from pubs.authors Create PROC usp_MainTable as select * into #NewTable From usp_subTable
<<dbname>>.<<username>>.<<storedproceduresname>> as it makes the SQL server’s scanning job easier.
sp_server_info : Gives complete information about the current SQL Server that you are connected to.
sp_databases, sp_helpdb – Lists all the databases, with this two sp_helpdb provides more precise and clear information on the size, status created date etc..,
sp_attach_db, sp_detach_db – These stored procedures are usefull when you are porting a database from one server to another server. This is one of the best methods to change the database without any problems. This is just like copying a file from one place to another place. The Operation is to Detach from the Source and Attach to the Destination. After this you may need to use sp_change_users_login to update the logins and users link.
sp_helpuser – Lists all the users in the system.
sp_who, sp_who2 – Gives the list of logged in users and their complete details about the execution status.
sp_msforeachtable – This is one of the undocumented procedures, which you can used to find the Total Physical Space Occupied by each tables in the database.
Eg:sp_msforeachtable ‘sp_spaceused “?”‘
sp_password – Used to change password for an SQL Server Login.
sp_tables – Lists all the tables and virtual tables (Views) along with the system tables, with information’s including table owner.
sp_stored_procedures – Lists all the stored procedures as like the table list with custom stored procedures in name; 1 format. (Please note it doesn’t mean that you can execute all the procedures that are listed, it depends on the permissions that you have)
sp_helptablename – Lists all the information pertaining to the given tablename with details about every column, constraints and the file table is located.
sp_helptext – Used to view the contents of a view, stored procedure, user defined functions.
sp_changeobjectowner – Changing Table / Stored Procedure Owner
If you ever want to change the owner of the object to another user this procedure will be helpful.
sp_change_users_login – This is a very useful procedure when you are porting the database using sp_attach_db and where you need to keep your old logins and users as same.
sp_change_users_login ‘Auto_Fix | Report | Update_One ’, ‘username’, ‘login_name’
The Auto_Fix is the best option in most of the cases where it automatically creates and links the login name to the given username. But it works only if the database user and the login are identical.
The Report option just gives information about the usernames and logins that the database has permission.
The Update_One option is used to specifically update the login of a database user.
Creating HTML Output is an option available in SQL Server 6.5 or above. This option helps us to create HTML Outputs for a given query. This option is useful if every want to create periodical reports. I would say this is the starting point for Microsoft to develop reporting services. There are three build in stored procedures available to create such tasks.
sp_makewebtask, sp_runwebtask, sp_dropwebtask each does that job as their name implies. More information on how to create and deploy web tasks can be found here.
Calling stored procedures from VB.NET