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 + '%'
Posted in Uncategorized. Leave a Comment »

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

Posted in Uncategorized. Leave a Comment »

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]

Posted in Uncategorized. Leave a Comment »

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;
        }
Posted in Uncategorized. Leave a Comment »

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
				);


Posted in Uncategorized. Leave a Comment »

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.

Posted in Uncategorized. 1 Comment »

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.

Posted in Uncategorized. Leave a Comment »