4elements, web design and consultancy

  1. 7 Handy SQL Scripts for SQL Developers

    A lot of things that we do depend upon the knowledge that we possess. If we are aware of what can be done, only then we can make smarter and more effective decisions. That is why it is always good to have quick tips and tricks handy in your pocket. This principle applies everywhere, including for MS-SQL developers. 

    Through this article I would like to share a few SQL scripts which have proven to be very useful for my daily job as a SQL developer. I'll present a brief scenario about where each of these scripts can be used along with the scripts below.

    Note: Before reaping the benefits from these scripts, it is highly recommended that all of the provided scripts be run in a test environment first before running them on a real-time database to ensure safety.

    1. Search for Text Inside All the SQL Procedures

    Can we imagine life without Control-F in today's world? Or a life without search engines! Dreadful, isn't it? Now imagine you have 20-30 sql procedures in your database and you need to find the procedure that contains a certain word. 

    Definitely one way to do it is by opening each procedure one at a time and doing a Control-F inside the procedure. But this is manual, repetitive, and boring. So, here is a quick script that allows you to achieve this.

    2. Compare Row Counts in Tables From Two Different Databases With the Same Schema

    If you have a large database and the source of data for your database is some ETL (extract, transform, load) process that runs on a daily basis, this next script is for you. 

    Say you have scripts that run on a daily basis to extract data into your database and this process takes about five hours each day. As you begin to look more deeply into this process, you find some areas where you can optimize the script to finish the task in under four hours. 

    You would like to try out this optimization, but since you already have the current implementation on a production server, the logical thing to do is try out the optimized process in a separate database, which you would replicate using the existing database. 

    Now, once ready, you would run both ETL processes and compare the extracted data. If you have a database with many tables, this comparison can take quite a while. So, here's a quick script that facilitates this process.

    3. Back Up Multiple Databases at Once

    In any IT company, the first thing a newly hired programmer (or sql developer) has to do before writing his or her first SQL query is buy insurance of the working version of the production database, i.e. make a backup.

    This single act of creating a backup and working with the backup version gives you the freedom to perform and practice any kind of data transformation, as it ensures that even if you blow off the company's client's data, it can be recovered. In fact, not just new hires but even the veterans from the same IT company never perform any data transformation without creating backups. 

    Although backing up databases in SQL Server is not a difficult task, it
    definitely is time-consuming, especially when you need to back up many databases at once. So the next script is quite handy for this purpose.

    4. Shrink Multiple Database Logs at Once

    Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction. The transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. 

    As the number of transactions starts increasing, however, space availability starts becoming a major concern. Fortunately, SQL Server allows you to reclaim the excess space by reducing the size of the transaction log.

    While you can shrink log files manually, one at a time using the UI provided, who has the time to do this manually? The following script can be used to shrink multiple database log files rapidly.

    5. Restrict Connection to the Database by Setting Single-User Mode

    Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance actions. Basically, if other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning. 

    This is quite useful in the scenarios where you need to restore your database to the version from a certain point in time or you need to prevent possible changes by any other processes accessing the database.

    6. String Function in SQL to Generate Dynamic Texts

    Many programming languages allow you to insert values inside string texts, which is very useful when generating dynamic string texts. Since SQL doesn't provide any such function by default, here is a quick remedy for that. Using the function below, any number of texts can be dynamically inserted inside string texts.

    7. Printing Table Column Definitions

    When comparing multiple databases that have similar schemas, one has to look at the details of table columns. The definitions of the columns (data types, nullables?) are as vital as the name of the columns themselves. 

    Now for databases having many tables and tables having many columns, it can take quite a while to compare each column manually with a column from another table of another database. The next script can precisely be used to automate this very process as it prints the definitions of all tables for a given database.

    Conclusion

    In this article, we looked at seven useful scripts that can cut down tons of manual, laborious work and increase overall efficiency for SQL developers. We also looked at different scenarios where these scripts can be implemented. 

    If you're looking for even more SQL scripts to study (or to use), don't hesitate to see what we've got available on CodeCanyon.

    Once you begin to get the hang of these scripts, certainly you will begin to identify many other scenarios where these scripts can be used effectively.

    Good luck!

     

    0 Comments

    Leave a comment › Posted in: Daily

0 Comments

Got anything to add?

(Basic HTML is fine)