Is it possible that I can query for names of all tables which contain column with name

SELECT COLUMN_NAME AS 'ColumnName' ,TABLE_NAME AS 'TableName' FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%MyName%' ORDER BY TableName,ColumnName;


Refer:  http://stackoverflow.com/questions/4849652/find-all-tables-containing-column-with-specified-name 

How to truncate transaction log file of a database in SQL Server

Cleaning templog  (tempdb transaction log file):
 
dbcc shrinkfile ('templog',0)

To look for  .mdf and .ldf  files and their locations. Use below statement:

select  *  from sys.database_files

How to kill SQL DB connections and bring it to offline and online back

ALTER DATABASE [Test]
SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE [Test]
SET ONLINE

In the Object Explorer tree drill down under Management to "Activity Monitor" (if you cannot find it there then right click on the database server and select "Activity Monitor"). Opening the Activity Monitor, you can view all process info. You should be able to find the locks for the database you're interested in and kill those locks, which will also kill the connection.



ref:  http://stackoverflow.com/questions/11620/how-do-you-kill-all-current-connections-to-a-sql-server-2005-database

Restore MS_SQL Database

I want to restore an MSSQL  .bak file which has multiple chunks inside it.

Below is the query to do that:

RESTORE DATABASE AMP

FROM DISK = 'C:\AMP2013\TMP_DB.bak'

WITH

MOVE 'TMP_DB' TO 'E:\TMP_DB_1.mdf',

MOVE 'FG_DM' TO 'E:\FG_DM_1.ndf',

MOVE 'FG_FNMP_Document_Content' TO 'E:\FG_FNMP_Document_Content_1.ndf',

MOVE 'FG_FNMP_History' TO 'E:\FG_FNMP_History_1.ndf',

MOVE 'FG_FNMP_Imported_Data' TO 'E:\FG_FNMP_Imported_Data_1.ndf',

MOVE 'ManageSoft_Log' TO 'E:\ManageSoft_log_1.ldf',

REPLACE,

STATS = 10