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