T-SQL to get Machine name from SQL Server Instance Name

How many of you use Central Management Server?
How many of you use CMS for just storing the list of servers?
How many of you use CMS for other activities other thank just storing the server names?

The list of servers registered under the CMS can be queried using the view "sysmanagement_shared_registered_servers" present in msdb database.

This will give the list of SQL Server Instance names, but when we require to get the Machine name from the SQL Server instance name, we can use this query to achieve it.

SELECT DISTINCT CASE WHEN CHARINDEX('\',server_name) = 0 THEN server_name
      ELSE SUBSTRING(server_name,1,CHARINDEX('\',server_name)-1) 
      END AS MachineName
FROM msdb.dbo.sysmanagement_shared_registered_servers

T-SQL query to get the list of currently executing queries in particular session

This query can be used as a alternative for using Profiler.

SELECT Sess.session_id, 
requests.status, 
requests.blocking_session_id 'Blocked By', 
requests.wait_type, 
wait_resource, 
requests.wait_time / (1000.0) 'Wait Time (sec)', 
requests.cpu_time, 
requests.logical_reads, 
requests.reads, 
requests.writes, 
requests.total_elapsed_time / (1000.0) 'Total Elapsed Time (sec)', 
SUBSTRING(st.TEXT,(requests.statement_start_offset / 2) + 1, 
((CASE requests.statement_end_offset 
WHEN -1 
THEN DATALENGTH(st.TEXT) 
ELSE requests.statement_end_offset 
END - requests.statement_start_offset) / 2) + 1) AS statement_text, 
COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid,st.dbid)) + N'.' + QUOTENAME(OBJECT_NAME(st.objectid,st.dbid)), 
'') AS command_text, 
requests.command, 
Sess.login_name, 
Sess.HOST_NAME, 
Sess.PROGRAM_NAME, 
Sess.last_request_end_time, 
Sess.login_time, 
requests.open_transaction_count 
FROM sys.dm_exec_sessions AS Sess
JOIN sys.dm_exec_requests AS requests
ON requests.session_id = Sess.session_id 
CROSS APPLY sys.Dm_exec_sql_text(requests.sql_handle) AS st 
WHERE requests.session_id <> @@SPID 
ORDER BY requests.cpu_time desc, requests.status, 
requests.blocking_session_id, 
Sess.session_id

Difference between ORIGINAL_LOGIN() and SUSER_SNAME()

The function ORIGINAL_LOGIN() , returns the name of the login that was initially connected to the SQL Server instance. We can use this function to return the identity of the original login in sessions in which there are many explicit or implicit context/connection switches.

The function SUSER_SNAME() , returns the name of the login that is currently connected to the SQL Server instance.

Here is small demo on working of ORIGINAL_LOGIN() and SUSER_SNAME()

-- Initial Details
SELECT ORIGINAL_LOGIN() AS [OriginalLoginSession], SUSER_SNAME() AS [CurrentLoginSession]
GO
-- Execute as another Login
EXECUTE AS LOGIN = 'Test'
GO
SELECT ORIGINAL_LOGIN() AS [OriginalLoginSession], SUSER_SNAME() AS [CurrentLoginSession]
GO
-- Revert the Execute as 
REVERT
GO
-- Later Details
SELECT ORIGINAL_LOGIN() AS [OriginalLoginSession], SUSER_SNAME() AS [CurrentLoginSession]
GO

Get list of users from a local group

This post is slightly deviating from my routine SQL scripts. :)

Here is the command to get the list of users present in a local group.
This is command shell script and the results can be directly written to a output file.

To get the list of users from local administrator group, run the below command from the command prompt.
net localgroup Administrators >C:\LocalAdministrators.txt

The same can be executed from SQL server using the xp_cmdshell extended stored procedure.

EXEC xp_cmdshell 'net localgroup Administrators'

Moving database objects between schemas

Sometimes database objects gets created under different schema names.
This can be intentional or accidental. In either of the cases, if you decide to move an database object between schema then you can make use of the below script.

To move a database object to between schema

ALTER SCHEMA [Target Schema Name] TRANSFER [SchemaName].[ObjectName]

To move a database object to dbo schema

ALTER SCHEMA [dbo] TRANSFER [SchemaName].[ObjectName]

To move multiple database objects between schema, execute the result set generated by the below query

SELECT 'ALTER SCHEMA [Target Schema Name] TRANSFER ['+SCHEMA_NAME([schema_id])+'].['+[name]+']' 
FROM sys.objects WHERE SCHEMA_NAME([schema_id]) NOT IN ('dbo', 'sys')