When Doing database Restore sometimes we may need to kill the spid’s in the database so that the connections are not open. Going through the spids in sp_who or sp_who2 can sometimes be a tedious task if there are more connections to the database. The following TSQL should generate the list of kill statements for [...]
May
18
2009
18
2009
Apr
27
2009
27
2009
Checking the Dependencies in Another Database
I have 20 Databases in my production Server and the tables in some database are used by the Views , functions and procedures of some Other Databases. Now I want to find out the Dependencies of the Tables and their Database Location. I can use the following Dynamic SQL to get the SQL statements and [...]
Apr
27
2009
27
2009
Splitting Website URL in TSQL
Recently I was involved in a task of splitting the URL’s and generating some reference based on them. I started looking around on google and found this function Split from http://www.otecode.com. This Table Valued function returns a list of substring based on the literal you specify as parameter create function [fn_Split] ( @text varchar(8000) ,@Delimiter [...]
Apr
23
2009
23
2009
Disk Space in Sql server
How to get the Disk Space in SQL Server? There are 2 ways a. xp_fixeddrives — The Easy way b. Ftsutil(xp_cmdshell) — If I want to know the percentage of free space in harddisk use FTSUTIL . The only thing you need to enable is the xp_cmdshell. Click here to read how to enable XP_cmdshell [...]
Apr
21
2009
21
2009
Enabling Xp_cmdShell in Sql server 2008
In Sql server 2008 the Surface area configuration no longer exists. you can enable the Xp_cmdshell either by the Policy based management or by using the sp_configure
Apr
15
2009
15
2009
Stored Procedures – Output Parameters
Here is how you create a stored procedures with OutPut Parameter The Execution is as follows
Apr
7
2009
7
2009
Output of a Procedure in a Table
There are cases when SQL developers want to filter the results of Procedures Output. This requires inserting the results of the procedure in the table. Here is how it can be done In the above example the temp table is created with the same columns as that of the procedure output, the Procedure is executed [...]
Apr
6
2009
6
2009
sp_depends – Dependencies for Objects
Getting the list of tables and Columns that are embedded in a view ,function or a procedure is simple sp_depends ‘viewName or ProcedureName‘ Documented in MSDN http://msdn.microsoft.com/en-us/library/ms189487.aspx
Apr
2
2009
2
2009
Using Update and Top in Sql server 2008
Sql Server 2008′s new TOP statement could help update the Table in batches Consider the following Example The above code illustrates how the batchUpdate can be done using Top(). The top 100 records are updated depending on the criteria where fullname is null so that the same records dont get updated again
Apr
1
2009
1
2009
Deprecated Features in SQL server 2008
Here are the List of features that are deprecated in Sql server 2008 http://msdn.microsoft.com/en-us/library/ms143729.aspx
