First set the database in question to single user to ensure no other connections are made to it

Then examine sys.database_files to get the file names of the database. Make a note of these names

USE <<database>>
select *
from sys .database_files

Detach the database using sp_detach_db.

EXEC sp_detach_db '<<database_name>>', 'true'

Refreshing the databases tab in SSMS will show the database disappear. It has not been deleted rather detached from management studio.

Now move the MDF and LDF files to your desired location.

Attach your database files using sp_attach_db.

EXEC sp_attach_db @dbname = N'<<database_name>>',
    @filename1 = N'M:\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Data\database_DB.mdf',
    @filename2 = N'M:\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Data\database_DB_log.ldf'

 

Refresh databases and you’ll see your newly moved databases appear in SSMS.

 

 

Lets say you’ve changed the name of your physical server and you want your sql server instance name to reflect this.

--THIS WILL SHOW YOU THE SERVERS 
select *
from sys .servers

--THIS WILL DROP THE SERVER FROM THE SERVER TABLE 
exec sp_dropserver 'OLD_SERVER_NAME'

--THIS WILL ADD THE NEW SERVER TO THE SERVERS TABLE, LOCAL WILL MAKE THIS SERVER_ID 0, THE INSTANCE YOU'RE ON
exec sp_addserver 'NEW_SERVER_NAME', 'local'

 

It goes without saying, be careful. Think carefully about what you’re dropping and adding 😉

 

Like the previous post (https://www.electricwaffle.co.uk/2013/02/using-t-sql-to-find-whos-in-what-database-role/) but this time with server roles 😉

SELECT p. NAME,m. NAME
FROM sys .server_role_members rm
JOIN sys .server_principals p
ON rm. role_principal_id = p .principal_id
JOIN sys .database_principals m
ON rm. member_principal_id = m .principal_id

 

This will return the principle against the server role they have.

 

 

 

Yes, you could go to the GUI but the T-SQL can be quicker.

use <<database>>

SELECT p. NAME
,m. NAME
FROM sys .database_role_members rm
JOIN sys .database_principals p
ON rm. role_principal_id = p .principal_id
JOIN sys .database_principals m
ON rm. member_principal_id = m .principal_id

 

This will return all the principles associated to the database in question against what role they have.

 

When examining the contents of sys.sysprocesses one of the very last columns in the table is labelled as the sql_handle. This is a very useful hex number which represents the T-SQL which was run during a process. We can use the inline table value expression dm_exec_sql_text() to translate this hex into text showing what T-SQL was run.

Very simply,

select *
from sys .dm_exec_sql_text( <<sql_handle goes here>> )

Its also possible to use dbcc inputbuffer with the spid from a process,

dbcc inputbuffer (<<spid goes here>>)

This will also return the T-SQL run but there is a limit to how many characters inputbuffer can display. If the command is long the end will be cut off.

Using T-SQL to ascertain the sizes of all the databases in an instance is really easy. The T-SQL below will show the sizes all of the databases at once in MB. This includes the sizes data and the log files combined for all databases.

SELECT DB_NAME(database_id) AS DatabaseName,
sum((size*8)/1024) as SizeOfDatabase
FROM sys.master_files
group by database_id

If you want to see all the database log file sizes

SELECT DB_NAME (mf . database_id) AS DatabaseName , sum ((size * 8)/ 1024 ) as SizeOfDatabase
FROM sys .master_files as mf
WHERE mf. type = 1 -- 1 refers to log
group by mf.database_id

If you want to see all the database data file sizes

SELECT DB_NAME (mf. database_id) AS DatabaseName, sum((size *8)/ 1024) as SizeOfDatabase
FROM sys .master_files as mf
WHERE DB_NAME (mf. database_id) in ( select d. name from sys.databases as d )
and mf. [type] <> 1 -- NOT 1 refers to log, anything not 1 is not a log
group by mf.database_id

Easy!