Put Scom Agents and Cluster Servers in Maintenance mode and reboot

Here is a script to put your scom agents including clusters in maintenance mode and rebooting them at a specified time. First you will have to create a task and a management pack in SCOM for rebooting the servers. Link on how to create the management pack is here. https://technet.microsoft.com/en-us/library/hh563486%28v=sc.12%29.aspx?f=255&MSPPError=-2147217396.
Works on System Center Operations Manager 2012 R2.

Create the input files and output files on the locations that you prefer. I have created them in C:\Reboots folder. Add your server names in the input file. Then the script given below can be put in a scheduled task and run at specified times or on demand. You can also run the script manually. Make sure that you modify the maintenance window.

I have not yet added any code to verify and alert if the rebooted servers are not back up.

Will probably do so at a later time. Till then feel free to use this one and modify any way that you like.

#################Script Start ####################################

$RootMS = "RMSName"
$Minutes =  90
$Comment = "Unknown"
#Setting up SCOM connection

Import-Module OperationsManager
$null = New-ScomManagementGroupConnection -ComputerName $RootMS
Add-PSSnapin "Microsoft.EnterpriseManagement.OperationsManager.Client" -ErrorAction SilentlyContinue

$eventLog = New-Object System.Diagnostics.EventLog("Operations Manager")
$eventLog.Source = "Maintenance Mode"

$Servers = GC "C:\Reboots\input.txt"
Function Out($output)
{
Out-File -Filepath "C:\Reboots\output.txt" -InputObject $output -Append
}

Out "###############  Starting Script at $(Get-Date)  ##########################"
foreach($Server in $Servers)
{

$output = "Starting for $Server on $(Get-date)"
Out $output

###Putting the agent and cluster in maintenance mode###########
$agent = Get-ScomAgent | Where-Object { $_.DisplayName –eq $Server -or $_.ComputerName -eq $Server -or $_.PrincipalName -eq $Server }
if(!$agent) { Write-Host "ERROR: $Server is not a monitored system in SCOM." -ForeGroundColor Red; Set-Location $originalPath; return }
$Server = $agent.PrincipalName
$startTime = (Get-Date).ToUniversalTime()
$endTime = $startTime.AddMinutes($Minutes)
if(($clusters = $agent.GetRemotelyManagedComputers())) {
$clusterNodeClass = Get-SCOMClass -Name Microsoft.Windows.Cluster.Node
foreach($cluster in $clusters) {
#$clusterObj = Get-SCOMClass -Name Microsoft.Windows.Cluster | Get-ScomMonitoringObject -Criteria "Name='$($cluster.ComputerName)'"
$clusterobj = Get-SCOMClass -Name Microsoft.Windows.Cluster | Get-SCOMClassInstance | ?{$_.displayname -eq $cluster.ComputerName}
if($clusterObj) {
$clusterObj.ScheduleMaintenanceMode($startTime,$endTime,"PlannedOther",$Comment,"Recursive")
$nodes = $clusterObj.GetRelatedMonitoringObjects($clusterNodeClass)
if($nodes) {
foreach($node in $nodes) {
Out "Putting $node into maintenance mode."
$eventLog.MachineName = $node.Name
$eventLog.WriteEntry("The server entered into maintenance mode $(if($Server -notcontains $node.Name){"on behalf of $Server"}).`r`n`r`nDuration:`t$Minutes minutes`r`nReason:`t$Comment","Information",42)
}
}
}
Out "Putting $($cluster.Computer) into maintenance mode."
New-MaintenanceWindow -StartTime $startTime -EndTime $endTime -MonitoringObject $cluster.Computer -Reason PlannedOther -Comment $Comment
}
}
else {
Out "Putting $Server into maintenance mode."
$eventLog.WriteEntry("The server entered into maintenance mode.`r`n`r`nDuration:`t$Minutes minutes`r`nReason:`t$Comment","Information",42)
New-MaintenanceWindow -StartTime $startTime -EndTime $endTime -MonitoringObject $agent.HostComputer -Reason PlannedOther -Comment $Comment
}
#####Sending commands to the server###########

$Task = Get-SCOMTask -DisplayName "Reboot Computer"
$Overrides = @{Arguments = '"$Target/Property[Type="MicrosoftWindowsLibrary7585010!Microsoft.Windows.Computer"]/PrincipalName$" "true"'}
$Instance = Get-SCOMClassInstance | ?{$_.Name -eq $agent.name}
Out "Starting task to reboot $($Instance.Displayname) at $(Get-Date)"
Start-SCOMTask -Task $Task -Override $Overrides -Instance $Instance

}

Out "###############  Script Complete at $(Get-Date) #########################"

#################Script End ####################################


I don't take credit for all the scripts that I written. Whenever I can credit the original creators, I do.  If I don't at times please do inform me so that I can include them in the page.

Visual Studio installation failing

Visual Studio 2015 installation failing.

Applies to 2013,2015. OS Windows 8.1

Installation kept failing with the following error in event log.

Faulting application name: vs_professional.exe, version: 14.0.23107.10, time stamp: 0x55414f16

Faulting module name: ntdll.dll, version: 6.3.9600.18233, time stamp: 0x56bb4e1d

Exception code: 0xc0000005

Fault offset: 0x0001dd93

Faulting process id: 0x14a8

Faulting application start time: 0x01d2105f30c9cadb

Faulting application path: C:\Parag\Softwares\VS2015\vs_professional.exe

Faulting module path: C:\WINDOWS\SYSTEM32\ntdll.dll

Report Id: 6e7b1bf9-7c52-11e6-8264-64006a57cea4

Faulting package full name:

Faulting package-relative application ID:

This error was seen in the installation log file located in

C:\Users\Parag\AppData\Local\Temp\dd_vs_professional_20160916160240.log

[1A94:1A98][2016-09-16T15:59:06]e000: Error 0x800705b4: Failed to wait for child to connect to pipe.
[1A94:1A98][2016-09-16T15:59:06]e000: Error 0x800705b4: Failed to connect to elevated child process.
[1A94:1A98][2016-09-16T15:59:06]e000: Error 0x800705b4: Failed to actually elevate.
[1A94:1A98][2016-09-16T15:59:06]e000: Error 0x800705b4: Failed to elevate.

Solution:

Copied the installation files to a local drive from the ISO. Right click the vs_professional.exe. Check the box for Run this program as an administrator.

Then run the exe again.

 

 

 

Audit collection Services

How to determine the Server being used for Audit Collection Forwarding and Collecting.

Open the operations manager console and navigate to Monitoring--Microsoft Audit Collection Services -- Collector or Forwarder. The state view should give you the names of the specific servers.

How to determine the SQL server being used for Audit Collection Database.

1. Log on to the management server with Administrator permissions.


2. Click Start, select Run, type regedit in the Open box, and then click OK to start Registry Editor.


3. Under HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\OpsMgrAC, check the name in  Server value.

How to zoom a page using touchpad on laptop

For those folks who would like to zoom in and zoom out of a page but do not have the luxury of an external mouse with a scroll button for a laptop. Worry no more.

Hold Ctrl key and drag two fingers across your touchpad. This will zoom out if you swipe upwards and zoom in if you swipe downwards. Tested on HP,Lenovo and Dell laptops.

Not sure about the others but go ahead and give it a try. If it works or doesn't ,do post in the comments with the make and model of your laptop to help others.

Contacts not syncing between Android and Outlook.com

I recently had to get a new phone because my trusty old Nokia WP stopped charging altogether. I broke the USB port on the phone.

They really should be having a backup port on the phones. It takes a whopping amount to repair once they are out of warranty.

Anyways, I got this new Android phone and added my Hotmail account. The contacts synced perfectly the first time. But then I had to factory reset it once and when I added the account I could not see any contact syncing. Not matter how many times I reconfigured my account on the phone. The contacts just did not sync. I stumbled on this post which talks about making changes to my windows live account.

http://answers.microsoft.com/en-us/outlook_com/forum/oemail-oapps/outlookcom-not-syncing-all-my-contacts-to-my/5a30f3dc-1bfb-475a-a67c-b282d1cd9c89

But sadly I did not have the time for someone to make changes to my account. So, I made a backup of my account contacts by logging to the account from a computer. Then deleted all the contacts from my windows live account. Waited a couple of minutes, signed out.

and signed back in. Not really sure if that was required, though. Imported all the contacts from file after cleaning up the duplicates using MSExcel's "Remove duplicates button" and without reconfiguring my account on the phone all the contacts started syncing.

Export all rules and monitors with thresholds

Exporting all rules and monitors with thresholds in your SCOM environment.

I found these on the following link

http://technet.microsoft.com/en-us/library/ff723847.aspx


[code language="css"]
##Export all rules

function GetPerfCounterName ([String] $configuration)
{
$config = [xml] ("<config>" + $configuration + "</config>")
return ($config.Config.ObjectName + "\" + $config.Config.CounterName)
}

function GetFrequency ([String] $configuration)
{
$config = [xml] ("<config>" + $configuration + "</config>")
$frequency = $config.Config.Frequency;

if($frequency -eq $null)
{
$frequency = $config.Config.IntervalSeconds;
}

return ($frequency)
}

function GetDisplayName($performanceRule)
{
if($performanceRule.DisplayName -eq $null)
{
return ($performanceRule.Name);
}
else
{
return ($performanceRule.DisplayName);
}
}

function GetWriteActionNames($performanceRule)
{
$writeActions = "";
foreach($writeAction in $performanceRule.WriteActionCollection)
{
$writeActions += " " + $writeAction.Name;
}
return ($writeActions);
}

$perf_collection_rules = get-SCOMRule # -criteria:"Category='PerformanceCollection'"

$perf_collection_rules | select-object @{name="Type";expression={foreach-object {(Get-MonitoringClass -id:$_.Target.Id).DisplayName}}},@{name="RuleDisplayName";expression={foreach-object {GetDisplayName $_}}} ,@{name="CounterName";expression={foreach-object {GetPerfCounterName $_.DataSourceCollection[0].Configuration}}},@{name="Frequency";expression={foreach-object {GetFrequency $_.DataSourceCollection[0].Configuration}}},@{name="WriteActions";expression={foreach-object {GetWriteActionNames $_}}}  | sort Type,RuleDisplayName,CounterName | export-csv  "C:\Outputs\CollectionRules.csv"

[/code]



[code language="css"]

## Export all monitors

function GetThreshold ([String] $configuration)
{
$config = [xml] ("<config>" + $configuration + "</config>")
$threshold = $config.Config.Threshold
if($threshold -eq $null)
{
$threshold = $config.Config.MemoryThreshold
}
if($threshold -eq $null)
{
$threshold = $config.Config.CPUPercentageThreshold
}
if($threshold -eq $null)
{
if($config.Config.Threshold1 -ne $null -and $config.Config.Threshold2 -ne $null)
{
$threshold = "first threshold is: " + $config.Config.Threshold1 + " second threshold is: " + $config.Config.Threshold2
}
}
if($threshold -eq $null)
{
if($config.Config.ThresholdWarnSec -ne $null -and $config.Config.ThresholdErrorSec -ne $null)
{
$threshold = "warning threshold is: " + $config.Config.ThresholdWarnSec + " error threshold is: " + $config.Config.ThresholdErrorSec
}
}

if($threshold -eq $null)
{
if($config.Config.LearningAndBaseliningSettings -ne $null)
{
$threshold = "no threshold (baseline monitor)"
}
}
return $threshold
}
Function GetFrequency ([String] $configuration)
{
$config = [xml] ("<config>" + $configuration + "</config>")
$Frequency = $config.Config.Frequency
if($Frequency -eq $null)
{
$frequency = $config.Config.Frequency;
}
return ($frequency)
}
Function GetNumsamples ([String] $configuration)
{
$config = [xml] ("<config>" + $configuration + "</config>")
$Samples = $config.Config.Samples
if($Samples -eq $null)
{
$Samples = $config.Config.NumSamples;
}
return ($Samples)
}
Function GetCounterName ([String] $configuration)
{
$config = [xml] ("<config>" + $configuration + "</config>")
$Counter = $config.Config.Counter
if($Counter -eq $null)
{
$Counter = $config.Config.CounterName;
}
return ($Counter)
}
Function GetObject ([String] $configuration)
{
$config = [xml] ("<config>" + $configuration + "</config>")
$Object = $config.Config.Object
if($Object -eq $null)
{
$Object = $config.Config.ObjectName;
}
return ($Object)
}
$perfMonitors = get-scommonitor
$perfMonitors | select-object @{Name="MP";Expression={ foreach-object {$_.GetManagementPack().DisplayName }}},@{name="Target";expression={foreach-object {(Get-SCOMClass -Id:$_.Target.Id).DisplayName}}},DisplayName,enabled,@{name="Threshold";expression={foreach-object {GetThreshold $_.Configuration}}}, @{name="Frequency";expression={foreach-object {GetFrequency $_.Configuration}}}, @{name="Samples";expression={foreach-object {GetNumSamples $_.Configuration}}}, @{name="Counter";expression={foreach-object {GetCounterName $_.Configuration}}}, @{name="Object";expression={foreach-object {GetObject $_.Configuration}}} | sort Target, DisplayName | export-csv "C:\Outputs\PerformanceMonitors.csv"

[/code]

Connecting to SCOM 2012 using powershell

Use the below snippet to connect to your OpsMgr powershell using Windows Powershell ISE.
There after you can use the cmdlets present in SCOM 2012 freely

## Common script for connecting to SCOM 2012 Management Server
$reg = Get-Item "HKLM:\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Agent Management Groups\SCOM2012PROD\Parent Health Services"
$RMS = $reg.GetValue("NetworkName")
Import-Module -Name OperationsManager
New-SCmanagementGroupConnection $RMS | Out-Null
Write-Host $RMS -foregroundcolor Red
########################################################################################

In organizations where you have multiple management groups. And have production environments which need SCOM to be working all the time. Its advisable to have two action accounts in the SCOM environment. When one account's password is near expiry you can switch to another account and change your healthservice to run from that account.
Its a big task to rotate the action accounts on all management groups and this is where my script comes handy. It helps change the default action account on SCOM 2012 and also changes the Data Warehouse Report Deployment Account. The script files are below.
Change the extension to .ps1 after downloading. The usage is given at the top of the scripts.

Om12ActionAccountRotate

Om12ActionAccountpasswordUpdate

Increase SQL server performance by using WIFI(Windows Instant File Initialization)

This is an interesting post I came across by Saleem Hakani. I have not tested it yet.
It promises to reduce the time for
Creating a database
Expand data files for additional growth
Automatic data file growth due to auto-grow settings
Restoring a database, etc.
http://sqlcommunity.com/SQL-ARTICLES/Post/1514/SQL-Server-File-Operations-on-Steroid"

MSI installation package did not run successfully.

At times you find that some msi installer that you ran did not install successfully.
You can enable MSI verbose logging by running below command and view the logs from %temp% folder.
You will have to add the below reg key to the machine you are installing the msi on.
Run this command in the admin prompt to add the key.

reg add HKLM\SOFTWARE\Policies\Microsoft\Windows\Installer /v Logging /t REG_SZ /d voicewarmup /f

Some Useful SQL queries

I have compiled below some SQL queries that I have been using regularly. I can't take credit for writing all of them, though.

/*get size and free space in a database*/
SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;

/*Get size of the database.*/
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
GO

/*Find which processes are blocking on the Sql Server.*/
select * from master.dbo.sysprocesses (nolock) where blocked 0 and spid blocked
Execute DBCC inputbuffer

/*Find blocking SPID*/
select distinct object_name(a.rsc_objid), a.req_spid, b.loginame
from master.dbo.syslockinfo a (nolock) join
master.dbo.sysprocesses b (nolock) on a.req_spid=b.spid
where object_name(a.rsc_objid) is not null

/*Disable All SQL Server Agent Jobs*/
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

/*Enable All SQL Server Agent Jobs*/
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE Enabled = 0;
GO

/*Disable Jobs By Job Name*/
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE '%Jobname%';
GO

/*Disable Jobs By Job Category*/
USE MSDB;
GO
UPDATE J
SET J.Enabled = 0
FROM MSDB.dbo.sysjobs J
INNER JOIN MSDB.dbo.syscategories C
ON J.category_id = C.category_id
WHERE C.[Name] = 'Database Maintenance';
GO


/*Find out SQL version in SQL 2000 and above.*/
SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'

/*Find out version of sql in 2008.*/
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')


/*Mirror is failed and cannot drop database. Database shows in recovery mode. Refer the following link: */
http://www.sqlnewsgroups.net/sqlserver/t20474-database-mirroring-problems-cant-drop-database.aspx

alter database dbname set partner off
drop database dbname

/*update statistics on all the tables:*/
Exec sp_MSForEachtable 'UPDATE STATISTICS ? WITH FULLSCAN'
GO

/*Get the table having the biggest,largest,huge size:*/

USE DBNAME
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT (*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

/*Another query for largest tables*/
declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)

create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)

set nocount on

-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'

open c_tables

fetch next from c_tables
into @id

while @@fetch_status = 0
begin

/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id

select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id


/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id

update #spt_space
set rows = i.rows
from sysindexes i
where i.indid = 0)
BEGIN
--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName

--Get the next table name
FETCH NEXT FROM tableCursor INTO @TableName
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT *
FROM #TempTable

--Final cleanup!
DROP TABLE #TempTable

GO
The above is the actual stored procedure, this must be created first then you simply call this one procedure to get the list of tables and sizes. At the bottom of this article you will find a zip file available for download that will include this script file.

Usage

Now to get the table sizes for the current database you can simply run the following command.

EXEC GetAllTableSizes
The results of this procedure execution will show you the number of rows and physical sizes of each user defined table in your database.
*******************************************************************************************************************************************
/*How to take a TRANSACTION LOG Backup:*/
BACKUP LOG Logname TO DISK = 'C:\Backup\Logname.trn'
sp_spaceused
DBCC SQLPERF(logspace)
DBCC OPENTRAN

/*Show blocking spids:*/
select c=sql_text.text, spid,blocked,waittime,cpu,physical_io, memusage, hostname, login_time, last_batch from master.dbo.sysprocesses
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where hostname ''
order by blocked desc

/*Find out longest running queries on a SQL server.*/
SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO
*************************************************************************************
Prior to SQL Server 2005, blocking could be detected using the sp_blocker_pss80 stored procedure, sp_who2, Perfmon and SQL Profiler. However, SQL Server 2005 has added some important new tools that adds to this toolkit. These tools include:
•Enhanced System Monitor counters (Perfmon)
•DMV's: sys.dm_os_wait_stats, sys.dm_os_waiting_tasks and sys.dm_tran_locks
•Blocked Process Report in SQL Trace
•SQLDiag Utility
*************************************************************************************
/*Find all user logins in a database*/
use dbname
select loginname from syslogins
*************************************************************************************
/*Check the owner of a database*/
select suser_sname(owner_sid) from sys.databases
*************************************************************************************
/*Change the owner of a database*/
EXEC sp_changedbowner 'Parag'
*************************************************************************************
/*Query to find relationship between tables*/
Select
object_name(rkeyid) Parent_Table,
object_name(fkeyid) Child_Table,
object_name(constid) FKey_Name,
c1.name FKey_Col,
c2.name Ref_KeyCol
From
sys.sysforeignkeys s
Inner join sys.syscolumns c1
on ( s.fkeyid = c1.id And s.fkey = c1.colid )
Inner join syscolumns c2
on ( s.rkeyid = c2.id And s.rkey = c2.colid )
Order by Parent_Table,Child_Table
*************************************************************************************
/*How to find if transcation logs are being restored*/
select distinct r.destination_database_name, r.restore_date,
bs.server_name, m.physical_device_name as backup_device, b.physical_name
from msdb.dbo.restorehistory r
inner join msdb.dbo.backupfile b on r.backup_set_id = b.backup_set_id
inner join msdb.dbo.backupset bs on b.backup_set_id = bs.backup_set_id
inner join msdb.dbo.backupmediafamily m on bs.media_set_id = m.media_set_id
*************************************************************************************
/*Increase mirror Timeout*/
ALTER DATABASE dbName SET PARTNER TIMEOUT 20
*************************************************************************************
/*Check when a table was last updated*/
select object_name(OBJECT_ID) as dbname,last_user_update,* from sys.dm_db_index_usage_stats

/*Script to find last run queries*/
SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC
*************************************************************************************
/*find if a column is present in all the tables in a database.*/
USE AdventureWorks
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;
*************************************************************************************
/*Find all columns in a table*/
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.tablename')
*************************************************************************************
/*Using loop in SQL.*/
Declare @loop int
set @loop = 1
select @loop
while (@loop != 5)
begin
--Put your query here.
set @loop = @loop+1
end
*************************************************************************************
/*Using charindex and patindex*/
USE AdventureWorks2012;
GO
DECLARE @document varchar(64);
SELECT @document = 'Reflectors are vital safety' +
' components of your bicycle.';
SELECT CHARINDEX('bicycle', @document); --- Gives the number where the character is found
GO
*************************************************************************************
/*Using substring to return part of a string*/
--SUBSTRING ( expression ,start , length )
USE AdventureWorks2012;
GO
SELECT LastName, SUBSTRING(FirstName, 1, 1) AS Initial
FROM Person.Person
WHERE LastName like 'Barl%'
ORDER BY LastName;

*************************************************************************************

/* Get processor counts from SCCM/MECM*/

SELECT
 DISTINCT(CPU.SystemName0) AS [System Name],
 CPU.Manufacturer0 AS Manufacturer,
 CPU.Name0 AS Name,
 COUNT(CPU.ResourceID) AS [Number of CPUs],
 CPU.NumberOfCores0 AS [Number of Cores per CPU],
 CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count]
 FROM [dbo].[v_GS_PROCESSOR] CPU
 GROUP BY
 CPU.SystemName0,
 CPU.Manufacturer0,
 CPU.Name0,
 CPU.NumberOfCores0,
 CPU.NumberOfLogicalProcessors0

 *************************************************************************************