Category Archives: Scripts

Get Database information in mail using PowerShell !

Posted on by

Would you love to get a weekly status report of all the databases hosted on your mission critical servers? ,Would you love to get a report which will help you maintain and manage your large shared SQLServer environments neat and clean?

If your answer is Yes, then powershell is your solution. Welcome to the world of powerful powershell(Well, that sounds like a good marketing buzz word !).

logo-powershell

Lets’ think about a large enterprise situation where in you are managing a huge SQLServer shared server which has many databases. Each day your DBA team members will be adding new databases to the server, and you would like to get a status report to ensure that the databases are compliant to the regular database ‘rules’ as you are responsible for the well being of the environment.

Example – No database can have a compatibility level which is less than 110,No databases can have owner other than SA etc.

I’m a great fan of the articles written by Microsoft Scripting Guy, Ed Wilson(B) and one of his articles gave me a quick idea of deploying a powershell function which will pull the database details on a weekly basis.

I slightly modified his code mentioned in the article here (This is a very useful/detailed article, and I highly recommend this to everyone ) and my little function will pull database information such as Name,PageVerify,CompatibilityLevel,Size,LastBackupDate,Recoverymodel,Owner etc and will send across an email.

Note – All credits goes to Ed for his original code. I have great respect for Ed,and if you are interested in powershell, then I highly recommend reading his articles.

Here is the powershell function   –

#Remember to make changes for SMTP area

Function Get-DBInfo ($SQLInstance)

{

    if ($SQLInstance.Contains(“`\”))

        { $location = “SQLSERVER:\SQL\$SQLInstance\Databases” }

    else

        { $location = “SQLSERVER:\SQL\$SQLInstance\DEFAULT\Databases” }

    $style = ‘<style>’

    $style = $style + ‘BODY{background-color:peachpuff;}’

    $style = $style + ‘TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}’

    $style = $style + ‘TH{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color:thistle;font-size:16}’

    $style = $style+ ‘TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:palegoldenrod;font-size:14}’

    $style = $style + ‘</style>’

        $body=dir -force  $location | where-object {$_.Name; $_.Refresh()} |

        Select Name, CompatibilityLevel,Pageverify,Size,LastBackupDate,RecoveryModel,Owner |ConvertTo-Html -Head $style -Property Name,CompatibilityLevel,Pageverify,Size,LastBackupDate,RecoveryModel,Owner |Out-String

        #Set your smtpserver information

        Send-MailMessage -To “anupsiva.das@abc.com” -Subject “Database Details from SQL2012 – Notification” –From “dba@abc.com” -SmtpServer “mailserver.abc.com” -Body $Body -BodyAsHtml

}

Get-DBInfo SQL2012

<Modified 01/27/2013> Above code was modified to pass the correct variable <Modified>

If you schedule this function as a SQL Job then you will receive automated emails according to your requirements.

Here is a sample email which was generated by the Get-DBInfo function.

Powershell Notification

Conclusion

Automated reports is going to help DBAs a lot and powershell is making life more easier by helping us to create highly flexible functions.

Thanks for reading and keep watching this space for more.

Capture Physical and Logical CPU details and Physical Memory using TSQL

Posted on by

Most of the time we need to capture the potential hardware capacity of the server which hosts SQL Server and below TSQL will help you to do this.

This script pulls the Logical,Physical CPU details and the Physical,Virtual Memory configurations.

SELECT cpu_count AS [Logical CPUs],
(case hyperthread_ratio
when 1 then
‘1’
else
(cpu_count / hyperthread_ratio)
end)AS [Physical CPUs],
hyperthread_ratio
,physical_memory_in_bytes / 1048576 AS ‘mem_MB’
,virtual_memory_in_bytes / 1048576 AS ‘virtual_mem_MB’
FROM sys.dm_os_sys_info

Rename a table,Easy using sp_rename,but how to use in C#

Posted on by

Today I came across an interesting question on how to rename a table in SQL Server.Thats a pretty easy one and we can use sp_rename to accomplish this.

sp_rename ‘OLD_TABLE_NAME’  , ‘NEW_TABLE_NAME’

However when a user needs it do via C#,then how can he/she do this.

Solution

 

To rename a table in a C# code, pass the sp_rename code as string to a SqlCommand object and call the SqlCommand.ExecuteNonQuery() method.

 

Pull Physical and Logical CPU info along with Memory

Posted on by

This is a handy script to pull info related to CPU and Memory.

SELECT cpu_count AS [Logical CPUs]
,cpu_count / hyperthread_ratio AS [Physical CPUs]
,physical_memory_in_bytes / 1048576 AS ‘mem_MB’
,virtual_memory_in_bytes / 1048576 AS ‘virtual_mem_MB’
,max_workers_count
,os_error_mode
,os_priority_class
FROM sys.dm_os_sys_info

World of DMV’s  are really fun and exciting.

Script the permissions of an OBJECT

Posted on by

Recently I came across a situation where in I had to scripyt out the permissions of all the stored procedures for a database.After lot of R&D came across a SP which was quite handy.
 
Solution :
 
sp_helprotect null, null,null,’o’ 
This SP will provide the required SP permissions when ran against the DB.
We can also write a cursor to accomplish this task and the code snippet is mentioned below :
DECLARE @name varchar(100)
DECLARE cur CURSOR local FOR
SELECT name from dbo.sysobjects
 WHERE type = ‘P’ AND category = 0
OPEN cur
FETCH NEXT FROM cur INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_helprotect @name
FETCH NEXT FROM cur INTO @name
END
CLOSE Cur
DEALLOCATE Cur