Find keyword used in Stored Procedure, Function and View in SQL SERVER

Here is the query to find any keyword used in definition of Stored Procedure, Function and View

Replace keyword in @Keyword Vairable

DECLARE @Keyword Varchar(50)
SET @Keyword = 'Association'
SELECT 'SP' as [Type], Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%' +@Keyword+ '%'
Union
SELECT 'VW' as [Type], Name FROM sys.views WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%' +@Keyword+ '%'
Union
SELECT 'FN' as [Type], ROUTINE_NAME as Name  FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' And ROUTINE_DEFINITION Like '%' + @Keyword + '%'
Union
SELECT 'SP' as [Type], ROUTINE_NAME as Name  FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' And ROUTINE_DEFINITION Like '%' + @Keyword + '%'

Find dependency refering a table in SQL SERVER

Here is the query to know the dependency of table in sql server.
Just replace the @TableName variable.

DECLARE @TableName Varchar(50)
SET @TableName = 'Association'
SELECT  
    DISTINCT 
    ISNULL(sd.referenced_schema_name+'.','')+ OBJECT_NAME(sd.referenced_id) As TableName,
    OBJECT_NAME(sd.referencing_id) As ObjectName,
    CASE 
	WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsUserTable') = 1  THEN'Table'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsTableFunction') = 1  THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsTableFunction') = 1  THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsScalarFunction') =1  THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsTrigger')= 1  THEN'Trigger'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsView')= 1  THEN'View'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsUserTable')= 1 THEN'Table'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsProcedure')= 1 THEN'Procedure'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsIndexed')= 1 THEN'Index'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsForeignKey')= 1 THEN'ForeignKey'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsPrimaryKey')= 1 THEN'PrimaryKey'
    END AS ObjectType
FROM    
    SYS.SQL_EXPRESSION_DEPENDENCIES SD
    INNER JOIN SYS.OBJECTS obj ON obj.object_id=sd.referenced_id
WHERE   
    obj.is_ms_shipped= 0
    AND referenced_id=object_id(@TableName) /*Where one can Replace table Name*/
    AND obj.type_desc='USER_TABLE'
ORDER BY 
    TableName,ObjectName,ObjectType

FInd text in Stored Procedure, Function, View or Trigger

Following query can be use to search any text in SQL Server –

SELECT DISTINCT
s.name+’.’+o.name AS Object_Name,o.type_desc, o.[Type]
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
INNER JOIN sys.schemas s ON o.schema_id=s.schema_id
WHERE m.definition Like ‘%FN_Accounts_GetLedgerOpeningBalance%’
–AND o.Type=’P’ –<uncomment if you only want to search procedures
–AND o.Type=’FN’ –<uncomment if you only want to search procedures
ORDER BY [Type]

Calculate number of months between two dates

Here is the function to calculate months between two dates –

        public static double GetMonths(DateTime from, DateTime to)
        {
            if (to.Ticks < from.Ticks)
            {
                DateTime temp = from;
                from = to;
                to = temp;
            }

            double percFrom = (double)(from.Day-1) / DateTime.DaysInMonth(from.Year, from.Month);
            double percTo = (double)to.Day / DateTime.DaysInMonth(to.Year, to.Month);

            double months = (to.Year * 12 + to.Month) - (from.Year * 12 + from.Month);
            return months - percFrom + percTo;
        }

Set window always on top and Make center in MFC

Add following code at the end of function OnInitDialog(). This will make window always on top and center on screen.

	GetWindowRect( rect );
	int screenWidth = GetSystemMetrics(SM_CXSCREEN);
	int screenHeight = GetSystemMetrics(SM_CYSCREEN);
	// now change the size, position, and Z order of the window.
	::SetWindowPos(m_hWnd ,       // handle to window
				HWND_TOPMOST,  // placement-order handle
				(screenWidth - rect.Width())/2,
				(screenHeight - rect.Height())/2,     // vertical position
				rect.Width(),  // width
				rect.Height(), // height
				SWP_SHOWWINDOW // window-positioning options
				);

Run your C# Application as Administrator in Windows 7/ Vista

The following manifest XML tells the .NET Framework to run the Assembly that you specify as Administrator within Windows Vista:

<?xml version="1.0" encoding="utf-8" ?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">
  <assemblyIdentity version="1.0.0.0"
  processorArchitecture="X86"
  name="MyAppExeName"
  type="win32" />
  <description>This is description of created application</description>
  <trustInfo xmlns="urn:schemas-microsoft-com:asm.v3">
    <security>
      <requestedPrivileges>
        <requestedExecutionLevel level="requireAdministrator" />
      </requestedPrivileges>
    </security>
  </trustInfo>
</assembly>

Now, there are two ways to make your program use this Manifest:

1. Rename it to (YourEXEName).manifest. The .NET Framework when executing the file will see the Manifest and handle its contents.
2. Embed the .manifest file into you EXE. This can be done by executing the following on visual studio command line (Command Prompt):
mt -manifest YourProgram.exe.manifest -outputresource:YourProgram.exe
If your assembly is strong named, you will be unable to embed the manifest into it as it would invalidate the strong naming.
Now your own C# application will prompt to run as Administrator in Windows Vista or Window 7.

in Visual studio 2010 you can also add manifest by adding by right click on project and select add new item and select manifest file from the list and modify this file as above xml.

Update Environment variable on remote machine using Management Object in C#

Following is the code to update environment variable in remote machine. By using this method we are not able to add new environment variable but we can update existing environment variable.

/// <param name="MachineName">Remote machine which environment variable need to update.</param>
/// <param name="username">username of remot machine which is use for login </param>
/// <param name="password">password of remote machine which is use for login</param>
/// <param name="VariableName">Varaible name which need to be update</param>
/// <param name="VariableValue">new value of varialbe</param>
public void UpdateEnvironmentVariable(string MachineName, string username, string password, string VariableName, string VariableValue)
{
    string ResultInfo = "";
    bool IsFound = false;
    ManagementObjectSearcher query = null;
    ManagementObjectCollection queryCollection = null;

    ConnectionOptions opt = new ConnectionOptions();

    opt.Impersonation = ImpersonationLevel.Impersonate;
    if (!chkLocal.Checked)
    {
        opt.EnablePrivileges = true;
        opt.Username = username;
        opt.Password = password;
        opt.Impersonation = ImpersonationLevel.Impersonate;
        opt.EnablePrivileges = true;
    }
    try
    {
        ManagementPath p = new ManagementPath("\\\\" + MachineName + "\\root\\cimv2");

        ManagementScope msc = new ManagementScope(p, opt);

        SelectQuery q = new SelectQuery("Win32_Environment");

        query = new ManagementObjectSearcher(msc, q, null);
        queryCollection = query.Get();

    

        ResultInfo += "\r\nTotal Count - " + queryCollection.Count;
        foreach (ManagementObject envVar in queryCollection)
        {
            if (envVar["Name"].ToString() == VariableName.Trim())
            {
                ResultInfo += "\r\n" + "System environment variable " + envVar["Name"] + " = " + envVar["VariableValue"];
                string OldValue = envVar["VariableValue"].ToString();
                envVar.SetPropertyValue("VariableValue", VariableValue.Trim());
                envVar.Put();
                IsFound = true;
                ResultInfo += "\r\n" + "Message:\tEnvironment variable update successfully";
                ResultInfo += "\r\n" + "OldValue: " + OldValue;
                ResultInfo += "\r\n" + "NewValue: " + VariableValue.Trim();
                break;
            }
        }
        if (IsFound == false)
            ResultInfo += "\r\nNot Found:\t" + txtVariableName.Text.Trim() + " not found in environment vairable";
    }
    catch (ManagementException Ex)
    {
        ResultInfo += "\r\nError:\t" + Ex.Message;
        ResultInfo += "\r\nStack:\t" + Ex.StackTrace;
    }
    catch (System.UnauthorizedAccessException Ex)
    {
        ResultInfo += "\r\nError:\t" + Ex.Message;
        ResultInfo += "\r\nStack:\t" + Ex.StackTrace;
    }
    catch (Exception Ex)
    {
        ResultInfo += "\r\nError:\t" + Ex.Message;
        ResultInfo += "\r\nStack:\t" + Ex.StackTrace;
    }
}

Folloiwng are some points to run above code successfully –
1. “Remote Procedure Call (RPC) Locator” and “Remote Procedure Call (RPC)” service should be start on remote machine.
– To start above services, type services.msc on run prompt and search for above services. Right click on each service and set it to automatic and start it.

2. Window Firewall should be configure or should be off on remote machine.

3. A user (which will access registry of remote machine) must be added with administrator privileges on remote machine. To add user with administrator privileges do following thing
– Right click on MyComputer and click on manage.
– Expand Local users and groups
– Double click on groups and then double click on administrator group.
– Add user (which will access registry of remote machine from another machine) in this group.

Update Registry or Environment Variable on Remote Machine in C#

Environment variable are available in “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment” in registry. By making a new entry in registry we can set environment variable on remote machine.
Here is the code to update the registry or set environment variable of the remote machine.

void UpdateRegistryOnRemotemachine(string RemoteMachineName)
{
    try
    {
        //retrieve Key information
        RegistryKey environmentKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, "\\\\" + RemoteMachineName).OpenSubKey(@"SYSTEM\CurrentControlSet\Control\Session Manager\Environment", true);

        if (environmentKey != null)
        {
            RegistryKey envRegistry = environmentKey;
            envRegistry.SetValue(txtVariableName.Text.Trim(), txtVariableValue.Text.Trim());
            envRegistry.Close();
            environmentKey.Close();                    
        }
    }
    catch (Exception Ex)
    {
        string ErrorMsg = "\r\nError:\t" + Ex.Message;
        ErrorMsg += "\r\nStack:\t" + Ex.StackTrace;
    }

}

To run above code successfully following settings must be done on remote machine –
1. “Remote Registry” service should be start on remote machine.
– To start “Remote Registry” service, type services.msc on run prompt and search for “Remote Registry”. Right click on “Remote Registry” service and set it to automatic and start it.

2. Window Firewall should be configure or should be off on remote machine.
3. A user (which will access registry of remote machine) must be added with administrator privileges on remote machine. To add user with administrator privileges do following thing
– Right click on MyComputer and click on manage.
– Expand Local users and groups
– Double click on groups and then double click on administrator group.
– Add user (which will access registry of remote machine from another machine) in this group.

Now by doing all above setting that user can update registry of remote machine.

Get unread mail count of Gmail in C#

Following code demonstrate that how to get count of unread mail of Gmail account in C#. Gmail provides link “https://mail.google.com/mail/feed/atom&#8221; to access unread mail by user authentication.

Following is code description.
First we declare a structure which will contain information of unread mails –

    struct UnreadMails
    {
        public string From { get; set; }
        public string Subject { get; set; }
        public DateTime MailModify { get; set; }
    }

Now following function will return collection of unread mail using authentication –

        Collection<UnreadMails> GetNewMails()
        {
            WebRequest webGmailRequest = WebRequest.Create(@"https://mail.google.com/mail/feed/atom");
            webGmailRequest.PreAuthenticate = true;

            NetworkCredential loginCredentials = new NetworkCredential(txtUserName.Text.Trim(), txtUserPassword.Text.Trim());
            webGmailRequest.Credentials = loginCredentials;

            WebResponse webGmailResponse = webGmailRequest.GetResponse();
            Stream strmUnreadMailInfo = webGmailResponse.GetResponseStream();

            StringBuilder sbUnreadMailInfo = new StringBuilder(); byte[] buffer = new byte[8192]; int byteCount = 0;

            while ((byteCount = strmUnreadMailInfo.Read(buffer, 0, buffer.Length)) > 0)
                sbUnreadMailInfo.Append(System.Text.Encoding.ASCII.GetString(buffer, 0, byteCount));

            XmlDocument UnreadMailXmlDoc = new XmlDocument();
            UnreadMailXmlDoc.LoadXml(sbUnreadMailInfo.ToString());
            XmlNodeList UnreadMailEntries = UnreadMailXmlDoc.GetElementsByTagName("entry");

            UnreadMails oUnreadMails = new UnreadMails();
            Collection<UnreadMails> clUnreadMails = new Collection<UnreadMails>();

            for (int _i = 0; _i < UnreadMailEntries.Count; ++_i)
            {
                oUnreadMails.Subject = (UnreadMailEntries[_i]["title"]).InnerText;
                oUnreadMails.From = (UnreadMailEntries[_i]["author"])["name"].InnerText + " <" + (UnreadMailEntries[_i]["author"])["email"].InnerText + ">";
                oUnreadMails.MailModify = DateTime.Parse((UnreadMailEntries[_i]["modified"]).InnerText);
                clUnreadMails.Add(oUnreadMails);
            }
            return clUnreadMails;
        }

Now call above function on button click to get count of unread mail. There is also a check in this function that if any unread mail count once then it will not count as new unread mail –

        DateTime LastCheck = DateTime.MinValue;
        private void btnLogin_Click(object sender, EventArgs e)
        {
            int NewUnreadMail = 0;
            DateTime LastUnreadMailDate = DateTime.MinValue;
            Collection<UnreadMails> clUnreadMails = GetNewMails();
            for (int i = 0; i < clUnreadMails.Count; i++)
            {
                if (clUnreadMails[i].MailModify > LastCheck)
                {
                    NewUnreadMail++;
                }
                if (clUnreadMails[i].MailModify > LastUnreadMailDate)
                    LastUnreadMailDate = clUnreadMails[i].MailModify;
            }
            LastCheck = LastUnreadMailDate;
            lblTotalUnreadMailCount.Text = "Total Unread Mail Count -  "+ clUnreadMails.Count.ToString();
            lblNewUnreadMailCount.Text = "New Unread Mail Count -  " + NewUnreadMail.ToString();
        }

Following is a image of UI of form –

Move window form without Titlebar in C#

I am going to describe two method of moving Window Form without title bar.

I Method – Using Windows API
Add following code in your .cs file

public const int WM_NCLBUTTONDOWN = 0xA1;
public const int HT_CAPTION = 0x2;

[DllImportAttribute("user32.dll")]
public static extern int SendMessage(IntPtr hWnd, int Msg, int wParam, int lParam);
[DllImportAttribute("user32.dll")]
public static extern bool ReleaseCapture();

Now write following code on MouseMove event of that control on which window form should move –

private void panel1_MouseMove(object sender, MouseEventArgs e)
{
        if (e.Button == MouseButtons.Left)
        {
                ReleaseCapture();
                SendMessage(Handle, WM_NCLBUTTONDOWN, HT_CAPTION, 0);
        }
}

II Method : Using C# Code
Add following variables in your .cs file.

private bool mouseIsDown = false;
private Point firstPoint;

Now handle MouseMove, MouseUp, MouseDown event of that window control on which window form should move.
Code is as follow –

private void panel1_MouseMove(object sender, MouseEventArgs e)
        {
            if (mouseIsDown)
            {
                // Get the difference between the two points
                int xDiff = firstPoint.X - e.Location.X;
                int yDiff = firstPoint.Y - e.Location.Y;

                // Set the new point
                int x = this.Location.X - xDiff;
                int y = this.Location.Y - yDiff;
                this.Location = new Point(x, y);
            }
        }

        private void panel1_MouseDown_1(object sender, MouseEventArgs e)
        {
            firstPoint = e.Location;
            mouseIsDown = true;
        }

        private void panel1_MouseUp(object sender, MouseEventArgs e)
        {
            mouseIsDown = false;
        }

Note: Drawback of second method is, when cursor will move below taskbar then window form will also go below taskbar.