SQL Server

DataBase Related Query

–TSQL to Make DB OFFLINE
–ALTER DATABASE ACR SET OFFLINE
–TSQL to Make DB OFFLINE
–ALTER DATABASE ActivityNew SET ONLINE
— TO Fetch Database details
SELECT Db_name(dbid),
name,
filename
FROM   sys.sysaltfiles
WHERE  filename NOT LIKE ‘D:\MSSQL\%’
AND dbid > 6
ORDER  BY 1

— TO Fetch Database details
SELECT ‘ALTER DATABASE ‘ + Db_name(dbid) + ‘ MODIFY FILE (NAME = ‘ + name + ‘, FILENAME = ”’ + filename + ”’)’
FROM   sys.sysaltfiles
WHERE  filename LIKE ‘%pms%’
–wHERE FilenAmE LIKE ‘C%SQLdata\%’
AND dbid > 6
ORDER  BY 1

SELECT DISTINCT ‘ALTER DATABASE ‘ + Db_name(dbid) + ‘ SET OFFLINE ‘
FROM   sys.sysaltfiles
WHERE  filename LIKE ‘D:%’
AND dbid > 6

——TO Change the File Path of a DB
–ALTER DATABASE Genie MODIFY FILE (NAME = Genie_Data, FILENAME = ‘D:\MSSQL\Data\Genienew.MDF’)
–ALTER DATABASE Genie MODIFY FILE (NAME = Genie_Log, FILENAME = ‘D:\MSSQL\Log\Genienew_1.LDF’)
——- to Find Size of the Database
SELECT name,
filename,
( size * 8 )            ‘Sizein KB’,
( ( size * 8 ) / 1024 ) ‘Sizein MB’
FROM   sys.sysaltfiles
ORDER  BY 1

—- To check a Column Exists
SELECT Object_name(object_id),
*
FROM   sys.columns
WHERE  name LIKE ‘%NAme%’

——- to total size of the Database
SELECT Db_name(dbid),
Sum(( ( size * 8 ) / 1024 )) ‘Sizein MB’
FROM   sys.sysaltfiles
GROUP  BY dbid
ORDER  BY 1

–—— to total size of server Instance
SELECT Sum(( ( size * 8 ) / 1024 )) / 1024 ‘Sizein GB’
FROM   sysaltfiles

———ShrinkDatabase———

SELECT ‘USE ‘ + name,
‘GO’,
‘DBCC SHRINKDATABASE(”’ + name + ”’ )’,
‘GO’
FROM   sysdatabases

———Size of Database

SELECT Sum(( ( size * 8 ) / 1024 )) / 1024 ‘Sizein GB’,
Sum(( ( size * 8 ) / 1024 ))        ‘Sizein MB’
FROM   sysaltfiles

–where filename like ‘%.ldf%’
SELECT Db_name(dbid),
name,
filename,
growth,
( size * 8 )                       ‘Sizein KB’,
( ( size * 8 ) / 1024 )            ‘Sizein MB’,
( ( ( size * 8 ) / 1024 ) / 1024 ) ‘Sizein GB’
FROM   sysaltfiles
–where filename like ‘%cpl%’
–where filename like ‘%log%’
ORDER  BY 5 DESC
–select @@version