Query - to script the steps to move user database files around

Many times we don't pay attention while creating a user database and end up creating database files where they are not supposed to be.. in such scenarios often we find ourselves moving file across drives so as to arrange them to match our settings (or liking's ?) 

To move files of the online user database, you have to perform certain steps in the specific order and it is documented quite nicely in MSDN

This is what we do

• Take DB offline
• Move files
• Modify old file location to the new one
• bring database online

The following script generates the statements for the  steps mentioned above.. Please note that on my server I wanted to move files for the databases with a name starting from test.. you will have to change the WHERE clause so that the scripts are generated for the databases of your choice

Query - to find specific column across tables and across database

Here is the situation my friend came up with -

list all the databases and tables with containing one specific column

he required this info because his project was under migration and they were looking for this kind of information .. The query shared here can be modified to find Most commonly used column name across databases

Query does 2 things

• Loops over databases having test at the start of their name
• Finds out the column across tables using Information_schema.Columns

Query - to find objects containing specific word

Sometime back we did one exercise where our objective was to find out the objects which don't work any more .. 

for example suppose we had created a view v_tab with a simple select statement fetching abc column from XYZ table .. later on say after 3-4 months that abc column got renamed to lmn but the view v_tab referring to table column as abc never got updated so that view now became obsolete .. You see what happened there and mind you this happens quite often when attention is not paid while deploying new changes to the database objects.. 

we used following approach to address this

Query - to peek into Maintenance plan History

Today, it's about maintenance plan and it's history

Often we run into situations where jobs executing maintenance plan does not show actual error that caused the failure. And more often than not in such scenarios browsing through maintenance plans history works like a charm.. 

Recently a friend of mine returning from his long vacation and ran into similar situation where his sql agent job didn't provide me sufficient information about the root cause of failure. And to make matter worse they had limitation put on the agent history retention which did not help either. Thankfully he had error notification setup which helped him listing out the names of the job that had failed.

Given below is what I wrote to help him out .. there might be easier way than this so fill me in if you happen to know about it 

Interview Question - Write the SQL script to delete/Truncate data from all the tables of the specific database

Recently, I came across this question where candidate had to write a query to empty all the tables from the specific database. Prima facie it feels like a cakewalk for the candidate but believe me it's not. Why not ? Because one must consider the relationships between the tables while writing the query for this question. It could be multilevel hierarchy that one needs to identify before even thinking about deleting the data from the child tables.

So the question gets divided into 2 parts 

• Identify the relationship hierarchy
• Start deleting the data from the bottom i.e from child tables to the parent tables

Now the real question, is it really necessary to identify the hierarchy between tables ? Isn't there any other way to perform this ?