January 2008 - Posts

• Using sp_depends to find all Stored Procedures which reference / use a Table

  We can use the sp_depends system stored procedure to find all stored procedures which reference / use a particular table. The following T-SQL command will return a list of all database objects which are referencing / using the Products table: EXEC sp_depends Products Since the result set(s) returned by executing the above T-SQL command consists of all database objects (and not only stored procedures) which depend upon the Products table, we need to only look at the records which have type as "stored procedure" to know of all stored procedures which reference / use the Product table. Similarly, we need to only look at the records which have type as "view" to know of all views which reference / use the Products table. Cheers, Raj ~~~ CODING FOR ETERNITY !!! ~~~ Posted Jan 20 2008, 03:10 PM by raj with 1 comment(s) Filed under: SQL Server

• Using sp_fkeys to find all Foreign Keys pointing to a Table

  We can use the sp_fkeys system stored procedure to find all foreign keys which point to a particular table. The following T-SQL command will return a list of all tables which are referencing the primary key of the Products table: EXEC sp_fkeys Products The sp_fkeys system stored procedure comes very handy when dealing with large databases which have hundreds of tables. Cheers, Raj ~~~ CODING FOR ETERNITY !!! ~~~ Posted Jan 20 2008, 01:50 PM by raj with 1 comment(s) Filed under: SQL Server

• T-SQL Query to Find the Second Highest Column Value in a Table

  One of my favorite technical interview question on SQL Server is "What query would you write to find the second highest column value in a table?" The question seems easy, however most developers fail to answer to this. If we had a table named Employee which had a column named Salary and we had to find the second highest Salary in the Employee table, the query for the same would be: SELECT TOP 1 Salary FROM (SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC) AS E ORDER BY Salary ASC If we ran the above query against the Employee table which had the following 5 rows: 1000 2000 3000 4000 5000 The subquery or the inner query would return the top 2 rows in descending Salary order which would be: 5000 4000 The outer query would then select the top 1 row from the subquery results in ascending Salary order which would be: 4000 Note that if we had to get the fourth highest Salary, we could do so by simply changing the subquery from TOP 2 to TOP 4 Cheers, Raj ~~~ CODING FOR ETERNITY !!! ~~~ Posted Jan 10 2008, 02:53 PM by raj with 52 comment(s) Filed under: SQL Server

• Finding Database Size using sp_spaceused Stored Procedure

  We can use the sp_spaceused stored procedure to find out exactly how much disk space is currently being used by a database. If we simply execute this stored procedure without passing any parameters, it returns the following 2 result sets: Result Set 1: database_name: Name of the current database. database_size: Size of the current database in megabytes. database_size includes both data and log files. unallocated space: Space in the database that has not been reserved for database objects. Result Set 2: reserved: Total amount of space allocated by objects in the database. data: Total amount of space used by data. index_size: Total amount of space used by indexes. unused: Total amount of space reserved for objects in the database, but not yet used. code for etetnity !!! is hosted with GoDaddy Shared Hosting Plan which only allows upto 200 MB of database disk space which is really very limited and Community Server can reach this limit in no time. Therefore I have to keep monitoring the database disk space usage regularly. However, since GoDaddy does not provide Remote Desktop or Enterprise Manager / Management Studio access to the database server, the only way to know this is by executing the sp_spaceused stored procedure through GoDaddy's web based Query Analyzer or through a custom built ASP.NET web page which executes this stored procedure using ADO.NET. You can use the same technique if you face similar restrictions by your hosting provider. For more info on the sp_spaceused stored procedure, click here. Cheers, Raj ~~~ CODING FOR ETERNITY !!! ~~~ Posted Jan 02 2008, 03:33 PM by raj with 2 comment(s) Filed under: GoDaddy, Community Server, SQL Server

• Installing Community Server 2007 with GoDaddy Shared Hosting Plan

  Planning to launch a new Community Server 2007 website with GoDaddy Shared Hosting Plan? Or facing problems setting up the same? If you wish to save yourself all the hassles, download and read this excellent CS 2007 Installation Guide for GoDaddy Shared Hosting Plan by Dave Stokes here. I had a hard time setting up my CS 2007 website @ code for eternity !!!, but Dave's Installation Guide came to the rescue. Thanks for the excellent work Dave. Cheers, Raj ~~~ CODING FOR ETERNITY !!! ~~~ Posted Jan 02 2008, 01:12 PM by raj with 2 comment(s) Filed under: GoDaddy, Community Server