A lot of things that we do depends upon the knowledge that we possess. If we are aware of what can be done, only then we can make smarter and effective decisions. That is why it is always good to have quick Tips and Tricks handy in your pocket. This principle applies everywhere and even 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. A brief scenario about where each of these scripts can be used is presented along with the scripts below.
SAFETY PRECAUTION: 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.
Search for a Text Inside All the Sql Procedures
Can we imagine a life without Ctrl+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 procedures that contains certain word. Definitely one way to do it is opening each procedure one at a time and doing a Ctrl+F inside the procedure. But this is manual, repetitive and boring. So, here is a quick script that allows you to achieve this.
Compare Row Counts in Tables From Two Different Databases With 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 5 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 under 4 hours. You would like to try out this optimization but since you already have the current implementation in 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 allows to facilitate this process.
Back Up Multiple Databases at Once
In any IT company, the first thing a new hire programmer (or sql developer) has to do before writing his first SQL query is buy an Insurance of the working version of the Production database i.e. take back up. This single act of creating back up and working with the back up version allows freedom to perform and practice any kind of data transformation as it ensures that even if he blows off 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 back ups. While taking back up of databases in SQL server is not a difficult task but it definitely is time consuming. Specially, when you need to back up many databases at once. So, the next script is quite handy for this purpose.
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 to reclaim the excess space by reducing the size of the transaction log. While once can shrink log files manually, one at a time using the UI provided, who has the time to do this manually isn’t it? The following script can be used to shrink multiple database log files rapidly.
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 certain point in time or you need to prevent possible changes by any other processes accessing the database.
String Function in SQL to Generate Dynamic Texts
Many programming languages allow inserting 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 to that. Using the function below, any number of texts can be dynamically inserted inside string texts.
Printing Tables Columns 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 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 7 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. 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!
by Sovit Poudel via Envato Tuts+ Code
No comments:
Post a Comment