All posts by Deepak

Microsoft Most Valuable Professional (MVP) award-winning Database Designer with 5 years of experience in designing, administering, developing & performance tuning relational databases and data warehouse. I possess strong leadership skills experience by serving as the Chapter Leader of Professional Association for SQL Server (PASS) which has around 50 members. I am passionate about solving business problems using my technical skills and hence I am interested in the field of Technology Consulting in Information Management.

Recent Objects – Without Triggers

I was thinking about writing a script to capture the objects that were either CREATED/DELETED or MODIFIED in the last 24hrs. Instead of directly querying the sys.objects I from where I can only get minimal information I decided to get a whole bunch of useful information using the SQL Server default trace.

By default the default trace will be running in all the SQL Servers since SQL 2005 onwards. It will capture minimal information without overloading the database. It will be placed in the same location as that of the SQL Server errorlog. The maximum file size is 20MB after which the trace will roll over into a new file. However, SQL Server can maintain only the 5 most recently created trace files. In addition a new trace file is generated whenever SQL Server is restarted.

Attach all user databases

I was doing an upgrade of one of our development servers to SQL Server 2008. To minimize the downtime I decided to attach all the user databases using script. Basically, I am attaching the db from the same location there were present earlier to being detached.
Consider the following example, If I have a database named “Test” in SQL 2005 that is currently residing in the following locations, C:databaseTest.mdf & C:databaseTest_log.ldf and another database named “Test_2008” in C:temptest_2k8.mdf & C:temptest2k8_log.ldf then the script I wrote would prepare the script to attach the database test from the same location.

Continue reading Attach all user databases

Function to Split Comma separated string to Integer

There are cases where we need to pass the input parameter to the stored procedure as a comma-separated string in order to avoid multiple db calls from happening. This helps in reducing the db calls thereby reducing the load on the db server.

The below function will split the given comma-separated string into integers and process the results.

Continue reading Function to Split Comma separated string to Integer