Find columns across database in SQL Server

Best way to find a column referenced in different tables in database using SQL Server

select t.name from sys.columns c

inner join sys.tables t

on c.object_id = t.object_id

where c.name = '<Column Name>'

Find all tables without Primary Keys

Find all tables without Primary Keys

This is very simple but effective script. It list all the table without primary keys.


SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableNameFROM sys.tablesWHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0ORDER BY SchemaName, TableName;GO


Thats all. Happy Querying :).

Find given text in all stored procedures in SQL Server

Recently, we were working on requirement and need to search a text in list of Stored Procedures. The funny thing, that stored procedure list is more than 2000.  So, I started googling, and find following ways.

1) 

SELECT 
       OBJECT_NAME(object_id), 
       OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%TEXT%'

2)

Select object_name(object_id), definition
From sys.sql_modules

Where definition like '%TEXT%' and objectpropertyex(object_id, 'isProcedure')=1


e.g. Lets take example, you need to search 100 in all stored procedure. Use following query.

1)  SELECT 
       OBJECT_NAME(object_id), 
       OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%100%'


2)

Select object_name(object_id), definition
From sys.sql_modules

Where definition like '%100%' and objectpropertyex(object_id, 'isProcedure')=1

That's it. You will get results matching criteria. Just Enjoy. Happy coding.

Exception : Path is too long - RESOLVED

While working on a problem today, we were getting following exception. I agree that we were using very long path but we can't do anything with folder structure. It was a client share.

Exception:

The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters.

   at System.IO.Path.SafeSetStackPointerValue(Char* buffer, Int32 index, Char value)

   at System.IO.Path.NormalizePathFast(String path, Boolean fullCheck)

   at System.IO.Path.NormalizePath(String path, Boolean fullCheck)

   at System.IO.Path.GetFullPathInternal(String path)

   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)

   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access)

     at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)

   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)

   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

   at System.Threading.ThreadHelper.ThreadStart()

After searching on internet, I came to know that standard .NET API doesn't support that. Hence we moved  back to COM or WinAPI era i.e. full power to play with system. One of the API that suit our requirement is : 

CopyFile

This is how we fixed our issue:

1. Copy respective file using above API to a temporary location.

2. Performed required operations.

3. Delete file from temporary location.

As we have a .NET based application, following is the way to use this API:

Step 1:

Declare P/I invoke statement

 [DllImport("kernel32.dll", CharSet = CharSet.Unicode)]

        static extern bool CopyFile(string lpExistingFileName, string lpNewFileName, bool bFailIfExists);

where 

lpNewFileName - New path including file name.

lpExistingFileName - is a LONG file path including file name.  It can be of two types:

long path is always prefixed with : "\\?\"  when using above API.

a) In case of local drive:  \\?\D:\very long path

b) In case of network drive:

"\\?\" prefix is used with paths constructed according to the universal naming convention (UNC). To specify such a path using UNC, use the "\\?\UNC\" prefix. For example, "\\?\UNC\server\share", where "server" is the name of the computer and "share" is the name of the shared folder. These prefixes are not used as part of the path itself. They indicate that the path should be passed to the system with minimal modification, which means that you cannot use forward slashes to represent path separators, or a period to represent the current directory, or double dots to represent the parent directory. Because you cannot use the "\\?\" prefix with a relative path, relative paths are always limited to a total of MAX_PATH characters.

Example:

\\?\UNC\serverXYZ\Myfoldershare\very long path

Step 2:

Call above API as normal method.

 CopyFile(reallyLongPath, destination, false);

That's it.  File will copy to your destination path. Now, you can play as needed.

Note: 

Many but not all file I/O APIs support "\\?\"; you should look at the reference topic for each API to be sure.

Impersonation in Console/Windows Application in same and cross domain

After Go live of any application, there are issues which occurred for certain users sometimes. To nail down these issues, we need to execute code under that user i.e. impersonate user identities and run application under his user ID.

In Web application, this is quite straightforward and same can be achieved using web.config using location attribute like:

<location path="<Page/Service Path>">

    <system.web>

      <identity impersonate="true" />

    </system.web>

  </location>

<location path="<Page/Service Path>">

    <system.web>

      <identity impersonate="true" userName="<user Name>" password="<Password>"/>

    </system.web>

  </location>

Now, question comes, how to achieve same in console/Windows application if needed. so.. Here you go:

For impersonation in Console/Windows application, best and simple way is to use P/I invoke.  Following sample you can use for same:

Add following method at Class level:

[DllImport("advapi32.dll", SetLastError = true, CharSet = CharSet.Unicode)]

        public static extern bool LogonUser(String lpszUsername, String lpszDomain, String lpszPassword,

            int dwLogonType, int dwLogonProvider, ref IntPtr phToken);  

In your method:

Same Domain

IntPtr tokenHandle = IntPtr.Zero;

            bool returnValue = LogonUser("<User Name>", "<Domain>", "<Password>", 2, 0, ref tokenHandle); 

            WindowsIdentity newId = new WindowsIdentity(tokenHandle);

            using (WindowsImpersonationContext impersonatedUser = newId.Impersonate())

            {

// Add code to be executed under that user

}


Cross Domain:

This is little tricky. Follow given steps: 

1. Create class Impersonation.cs

2. Add following P/I invokes and enums

[DllImport("advapi32.dll", SetLastError = true)]
        private static extern int LogonUser(
        string lpszUserName,
        string lpszDomain,
        string lpszPassword,
        int dwLogonType,
        int dwLogonProvider,
        ref IntPtr phToken);

        [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        private static extern int DuplicateToken(
        IntPtr hToken,
        int impersonationLevel,
        ref IntPtr hNewToken);

        [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        private static extern bool RevertToSelf();

        [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
        private static extern bool CloseHandle(
        IntPtr handle);

        private const int LOGON32_LOGON_INTERACTIVE = 2;
        private const int LOGON32_PROVIDER_DEFAULT = 0;

        enum LogonType
        {
            Interactive = 2,
            Network = 3,
            Batch = 4,
            Service = 5,
            Unlock = 7,
            NetworkClearText = 8,
            NewCredentials = 9
        }
        enum LogonProvider
        {
            Default = 0,
            WinNT35 = 1,
            WinNT40 = 2,
            WinNT50 = 3

        }

3. Use following method

        public void ImpersonateUser(

        string userName,

        string domain,

        string password)

        {

            WindowsIdentity tempWindowsIdentity = null;

            IntPtr token = IntPtr.Zero;

            IntPtr tokenDuplicate = IntPtr.Zero;

            try

            {

                if (RevertToSelf())

                {

                    if (LogonUser(

                    userName,

                    domain,

                    password,

                    (int)LogonType.NewCredentials,

                    (int)LogonProvider.WinNT50,

                    //LOGON32_LOGON_INTERACTIVE,

                        //LOGON32_PROVIDER_DEFAULT,

                    ref token) != 0)

                    {

                        if (DuplicateToken(token, 2, ref tokenDuplicate) != 0)

                        {

                            tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);

                            impersonationContext = tempWindowsIdentity.Impersonate();

                        }

                        else

                        {

                            throw new Win32Exception(Marshal.GetLastWin32Error());

                        }

                    }

                    else

                    {

                        throw new Win32Exception(Marshal.GetLastWin32Error());

                    }

                }

                else

                {

                    throw new Win32Exception(Marshal.GetLastWin32Error());

                }

            }

            finally

            {

                if (token != IntPtr.Zero)

                {

                    CloseHandle(token);

                }

                if (tokenDuplicate != IntPtr.Zero)

                {

                    CloseHandle(tokenDuplicate);

                }

            }

        }

4. Create a instance of class as given below and add your code segment 

using (Impersonator imperso = new Impersonator("<userName>","<domain>","Password"))
{
 //Add your code here

}

That's it! ... You are all set to execute your code section under that user.

Thanks!

ActiveX Control not working .Net 4.5

We recently moved our application from .Net 4.0 to .Net 4.5. After that, our few component which were based on ActiveX component stopped working IE.

After a lot research , we found a solution. As with launch of .Net, Hosting managed controls inside IE is no longer supported out of the box.

Further Details : http://msdn.microsoft.com/en-us/library/hh367887.aspx

Here is one way that you can use if you want to load ActiveX Control in IE 10. 

Solution:


There is registry key called as "EnableIEHosting". This key needs to be added at two location:

• HKLM\SOFTWARE\MICROSOFT\.NETFramework
• HKLM\SOFTWARE\Wow6432Node\Microsoft\.NETFramework

Please create key of type "dword" and set its value to 1 e.g.

"EnableIEHosting"=dword:00000001

Thats it! You ActiveX controls are all set to load in IE 10 on any Windows operating system with .NET 4.5

You can create these entries either using code or simple run a batch file. 

Create Batch File :


REGEDIT4

; @ECHO OFF
; CLS
; REGEDIT.EXE /S "%~f0"
; EXIT

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework]
"EnableIEHosting"=dword:00000001


[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework]
"EnableIEHosting"=dword:00000001

Pause

Copy this content and save it in file ".bat" extension.  No code needed :)

Hope it helps!

jQuery Autocomplete with image

Recently, we were working on requirement where we need a search result like "facebook friend search" which returns in following format:

To get it working, we used jQuery's Autocomplete feature. Three steps to implement same.

1. A Text box.

2. You need data to display. If you are searching using a service, you can  call using Ajax that returns a set of results and bind it.

3. Display in following a way is tricky :)  i'll explain in detail as given below.


Lets start :

Step 1. Create a Text-box as follows:

<body>
  <input id="friends" />
</body>

Step 2. Call service using Ajax and prepare data-set to bind.


  $("#friends").autocomplete({
                source: function (request, response) {

                    $.ajax({
                        url: '<Service Url>',
                        type: "GET",
                        dataType: "json",
                        contentType: "application/json; charset=utf-8",
                        success: function (data) {
                            response($.map(data.Data, function (item) {
                                return {
                                    Name: item.Name,
                                    ImageUrl: item.ImageUrl
                                }
                            }));
                        }
                    });
                },
                minLength: 2,
                select: function (event, ui) {
                    log(ui.item ?
"Selected: " + ui.item.label :
"Nothing selected, input was " + this.value);
                },
                open: function () {
                    $(this).removeClass("ui-corner-all").addClass("ui-corner-top");
                },
                close: function () {
                    $(this).removeClass("ui-corner-top").addClass("ui-corner-all");
                }
            });

Note: source method is populated with results returned by service call. We have name and image url  there. It creates a a list of data as needed.

We can also assign some static data if needed like:


$(function() {
    var availableTags = [
      "x",
      "y",
      "y",
      "z"
    ];
    $( "#friends" ).autocomplete({
      source: availableTags
    });


Step 3: Create result like Facebook i.e. Image with text.

jQuery's data attribute and renderItem function finish that job for us.it can be used to create custom html in response flowing out as a result. We can apply required CSS as needed.


 $("#friends").autocomplete({
                source: function (request, response) {

                    $.ajax({
                        url: '<Service Url>',
                        type: "GET",
                        dataType: "json",
                        contentType: "application/json; charset=utf-8",
                        success: function (data) {
                            response($.map(data.Data, function (item) {
                                return {
                                    Name: item.Name,
                                    ImageUrl: item.ImageUrl
                                }
                            }));
                        }
                    });
                },
                minLength: 2,
                select: function (event, ui) {
                    log(ui.item ?
"Selected: " + ui.item.label :
"Nothing selected, input was " + this.value);
                },
                open: function () {
                    $(this).removeClass("ui-corner-all").addClass("ui-corner-top");
                },
                close: function () {
                    $(this).removeClass("ui-corner-top").addClass("ui-corner-all");
                }
            }).data( "autocomplete" )._renderItem = function( ul, item ) {
        var inner_html = '<a><div class="list_item_container"><div class="image"><img src="' + item.ImageUrl + '"></div><div class="label">' + item.Name + '</div></div></a>';
        return $( "<li></li>" )
            .data( "item.autocomplete", item )
            .append(inner_html)
            .appendTo( ul );
    };
});


Thats it! Task done.. Thanks to jQuery and its power.. simply awesome.