How To Find Dependent Tables In SQL Server

Here's a query that will list all dependent tables and fields of a table you specify. That is, foreign keys and column names. This might be useful if you need to delete a record from the master table and have to delete records from its dependent tables first.

Just replace myTableName in the WHERE clause accordingly.

SELECT
pt.COLUMN_NAME AS primaryField,
c.CONSTRAINT_NAME AS keyName,
fk.TABLE_NAME AS foreignTable,
cu.COLUMN_NAME AS foreignField
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk ON
 c.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ON
 c.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON
 c.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
 SELECT tc.TABLE_NAME, kcu.COLUMN_NAME
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
 ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
 WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pt ON pt.TABLE_NAME = pk.TABLE_NAME
WHERE pk.TABLE_NAME = 'myTableName'
ORDER BY foreignTable ASC;

OutputHere's a sample of the output you'll get:

Problem

ref: http://www.mssqltips.com/sqlservertip/1294/listing-sql-server-object-dependencies/

When developing components for SQL Server one change may affect another database object. Finding these dependent objects should be straightforward, but in most cases it is not as easy as you would think. So what is the best way to find dependency information in SQL Server?

Solution

There are several methods of getting this information. The first approach would be to use the SQL Server Management tools.
For SQL Server 2005, right click on the table name and select "View Dependencies" as shown below we are looking at dependencies for a table.

This will give you the following view so you can see objects that are dependent on the Employee table. 

And this next screen shot shows you objects that table Employee depends upon. 

To get this information, SQL Server does a lot of work to get.  To see the process that SQL Server uses to generate this data for this screen click here.

Although this is helpful to get the data via the GUI, what other approaches are there to get this data?

Method 1 - INFORMATION_SCHEMA.ROUTINES

This approach uses INFORMATION_SCHEMA.ROUTINES to search through the definition of the routine such as the stored procedure, trigger, etc...

SELECT DISTINCT routine_name, routine_type FROM INFORMATION_SCHEMA.ROUTINES WHERE         ROUTINE_DEFINITION LIKE '%UserPartnerPreference%'

Method 2 - sp_depends

This approach uses the system stored procedure sp_depends.

  
EXEC sp_depends @objname = N'yourTableName' ;

Method 3 - Using syscomments

This approach reads data from the syscomments table. This is similar to method 1.

SELECT distinct so.name 
FROM syscomments sc 
INNER JOIN sysobjects so ON sc.id = so.id 
WHERE charindex('yourTableName', text) > 0

Method 4 - sp_MSdependencies

This approach uses the system stored procedure sp_MSdependencies.

-- Value 131527 shows objects that are dependent on the specified object
EXEC sp_MSdependencies N'yourTableName', null, 1315327

-- Value 1053183 shows objects that the specified object is dependent on
EXEC sp_MSdependencies N'yourTableName', null, 1053183

C# Get ALL Image File In a Folder

 

protected List GetFilesInFolder(string folderVirtualPath)

{

string physicalPathToFolder = Server.MapPath(folderVirtualPath);// Get the physical path

string filterExpression = "*.gif";

string[] physicalPathsCollection = System.IO.Directory.GetFiles(physicalPathToFolder,

filterExpression);// Get all child files of the given folder

List virtualPathsCollection = new List();// Contains the result

foreach (String path in physicalPathsCollection)

{

// The value of virtualPath will be similar to '~/PathToFolder/Image1.jpg

string virtualPath = VirtualPathUtility.AppendTrailingSlash(folderVirtualPath) +

System.IO.Path.GetFileName(path);

virtualPathsCollection.Add(virtualPath);

}

return virtualPathsCollection;

}

JSON:Fat-Free Data Interchange

JSON stands for "JavaScript Object Notation" and is a lightweight data-interchange format. JSON is easy to generate and parse but also easily human-readable. JSON has a number of advantages in the JavaScript/client environment:

• JSON can be used as an easy-to-work-with alternative to XML.
• JSON can be de-serialized into objects and the objects serialized back into strings. There are API's that can do these transformations on both the client and server.
• Webservices can return JSON automatically for immediate use within JavaScript

JSON supports the usual basic type flavors: numbers, strings, booleans, arrays, objects and null.

The quickest way to understand how the JSON syntax works is to look at an example. Below is a sample JSON object definition called "contact". It has string properties for "firstName" and "lastName". Another property, "address" is an object that has its own properties for "streetAddress", "city", "state" and "postalCode". These address properties are all string except "postalCode" that contains a numeric value. The last property "phoneNumbers" is actually an array of strings.

[JavaScript] JSON Sample

var contact = {

// string property

"firstName": "John",

"lastName": "Smith",

// address property with sub-properties

"address": {

"streetAddress": "21 2nd Street",

"city": "New York",

"state": "NY",

// numeric property

"postalCode": 10021

},

// array

"phoneNumbers": [

"212 555-1234",

"646 555-4567"

]}

;

As you can see in the sample above, the JSON object definition appears between curly braces. Each property and value pair are separated by a colon. Arrays are contained within square brackets.

Using JSON Objects

Once the JSON object is defined you can assign and retrieve values using the properties of the object. In this next sample the "contact" object is assigned a new first and last name and the second element of the phoneNumbers array is also replaced with a new value.

[JavaScript] Assigning and Retrieving JSON Properties

// change the name and phoneNumbers properties

contact.firstName = "Bob";

contact.lastName = "Jones";

contact.phoneNumbers[1] = "123 555-9999";

alert(contact.firstName + ' ' +

contact.lastName + ' phone: ' + contact.phoneNumbers[1]);

Running this bit of JavaScript fires the alert shown below:

Serializing JSON

You can also take a JSON string and transform it into an object. The ASP.NET AJAX Library includes a JavaScriptSerializer object within the Sys.Serialization namespace that you get for free when you include a ScriptManager on the page. If you call the JavaScriptSerializer deserialize() method and pass a JSON string, the method will deserialize the string into a JSON object. Call the serialize() method to transform the a JSON object back to a string.

The sample below shows a JSON string defined for "contact". This is exactly the same as the "contact" object defined in the last example, but surrounded with quotes. A call to deserialize() takes the contact JSON string and transforms it into an object representation. Following that, the contact object is converted back using the serialize() method into its string representation.

[JavaScript] Serialize and Deserialize

var contactString = '{"firstName": "John", "lastName": "Smith", ' +

'"address": {"streetAddress": "21 2nd Street",' +

'"city": "New York","state": "NY", "postalCode": 10021},' +

'"phoneNumbers": ["212 555-1234","646 555-4567"]}';

// deserialize JSON string to an object

contact =

Sys.Serialization.JavaScriptSerializer.deserialize(contactString);

// serialize the contact JSON into a string

var contactStrings =

Sys.Serialization.JavaScriptSerializer.serialize(contact);

[C#] Serializing and Deserializing in Code-Behind

protected void Page_Load(object sender, EventArgs e)

{

Contact contact = new Contact();

contact.FirstName = "Bob";

contact.LastName = "Smith";

contact.Address.City = "San Francisco";

contact.Address.State = "California";

contact.Address.StreetAddress = "123 Telerik Ave";

contact.Address.PostalCode = 91234;

contact.PhoneNumbers.Add("123 555-1234");

contact.PhoneNumbers.Add("444 555-9876");

JavaScriptSerializer jss = new JavaScriptSerializer();

string contactString = jss.Serialize(contact);

tbServerStatus.Text = contactString;

Contact contact2 = jss.Deserialize
<contact>(contactString);

tbServerStatus.Text += System.Environment.NewLine + System.Environment.NewLine +

contact2.FirstName + " " + contact2.LastName;

}
 

Session Timeout with Warning and jQuery Session Refresh in ASP.Net

ASP.Net applications are written in such a way that after the session times out, the user is also logged out. This is sometimes to secure the application from others accessing the computer, while the real user is away from their desk. If this is the case, it’s nice to let the user know how long they’ve got left before they’re logged out due to inactivity. I got this from This Site Written in Cold Fusion.
I Converted To work in C# and Added Progress Bar With in Message Box.

Download

Summary

- User logs in, session created.
- Session set to time out (e.g. 30 minutes).
- When session times out, the user is logged out.
- Display a countdown so the user knows how long is left.
- Inform the user when they are approaching the limit (e.g. 5 minutes left).
- Let the user know that they’ve been automatically timed out due to inactivity.

Approach taken

Every request to the application will set two cookies:
Date / time the session will expire.
Current server date / time.
Our bit of JavaScript watches these cookies.
Any application activity will update these cookies so we know if a second window has done something and the session timeout refreshed.

sessionTimeout()

This is my first real effort at creating a jQuery plugin. It took a bit more effort wrapping everything up nicely and providing options but I think it was worth it and should make the code easier to include.

   (function($) {
    $.fn.sessionTimeout = function(options) {
        var opts = $.extend({}, $.fn.sessionTimeout.defaults, options);
        var inter = this.data('timer');
        if (inter) {
            clearInterval(inter);
        }

        var info = {
            warned: false,
            expired: false
        };
        processCookie(info, opts);

        this.data('timer', setInterval(cookieCheck, opts.interval, this, info, opts));
        cookieCheck(this, info, opts);
    };

    function processCookie(info, opts) {
        info.serverTime = Date.parse($.fn.sessionTimeout.readCookie(opts.timeCookie));
        info.sessionTime = Date.parse($.fn.sessionTimeout.readCookie(opts.sessCookie));
        info.offset = new Date().getTime() - info.serverTime;
        info.expires = info.sessionTime + info.offset;
        info.duration = Math.floor((info.sessionTime - info.serverTime) / 1000);
    };

    // private
    function cookieCheck(els, info, opts) {
        var sessionTime = Date.parse($.fn.sessionTimeout.readCookie(opts.sessCookie));
        if (sessionTime != info.sessionTime) {
            processCookie(info, opts);
        }
        info.timeLeft = {};
        var ms = info.expires - (new Date().getTime());
        info.timeLeft.minutes = Math.floor(ms / 60000);
        info.timeLeft.seconds = Math.floor(ms % 60000 / 1000);
        info.timeLeft.onlySeconds = info.timeLeft.minutes * 60 + info.timeLeft.seconds;
        info.timeLeft.minutes = info.timeLeft.minutes.toString().replace(/^([0-9])$/, '0$1');
        info.timeLeft.seconds = info.timeLeft.seconds.toString().replace(/^([0-9])$/, '0$1');
        if (!info.warned && info.timeLeft.onlySeconds <= opts.warningTime) {
            info.warned = true;
            opts.onWarning(els, info, opts);
        } else if (!info.expired && info.timeLeft.onlySeconds < 0) {
            info.expired = true;
            opts.onExpire(els, info, opts);
        }
        if (!info.expired) {
            opts.onTick(els, info, opts);
        }
    };

    function onTick(els, info, opts) {
        els.each(function() {
            opts.onTickEach(this, info, opts);
        });
    };

    function onTickEach(el, info, opts) {
        var pval = ((info.timeLeft.minutes * 60) + parseInt(info.timeLeft.seconds)) * 100 / opts.warningTime;

        $(el).html(info.timeLeft.minutes + ':' + info.timeLeft.seconds); //+ ' ' + opts.warningTime + ' ' + pval + '  ' + info.duration);
        if (pval < 100) {
            if (!$("#Session-TimeOut").dialog('isOpen'))
                $("#Session-TimeOut").dialog('open');
            $(".bar").progressbar({
                value: pval
            });
        }
        else {
            if ($("#Session-TimeOut").dialog('isOpen'))
                $("#Session-TimeOut").dialog('close');
        }
    };

    function onWarning(el, info, opts) {
        //alert('Warning');
        $("#Session-TimeOut").dialog('open');

    };

    function onExpire(el, info, opts) {
        window.location('Login.aspx');
        //alert('Expired');
    };

    // public
    $.fn.sessionTimeout.readCookie = function(name) {
        var nameEQ = name + "=";
        var ca = document.cookie.split(';');
        for (var i = 0; i < ca.length; i++) {
            var c = ca[i];
            while (c.charAt(0) == ' ') c = c.substring(1, c.length);
            if (c.indexOf(nameEQ) == 0)
                return unescape(c.substring(nameEQ.length, c.length));
        }
        return null;
    }

    $.fn.sessionTimeout.defaults = {
        timeCookie: 'SERVERTIME',//cookie 
        sessCookie: 'SESSIONTIMEOUT', //cookie
        interval: 1000,
        onTick: onTick,
        onTickEach: onTickEach,
        warningTime: 340, // seconds
        onWarning: onWarning,
        onExpire: onExpire
    };
})(jQuery);

The plugin provides .sessionTimeout(options) which is used on your selected elements to display the amount of time left until session expiration. The options allow you to use different cookie names, change the execution interval and override several events.
It works by setting up a periodic function to watch the cookies. Whenever the cookies are updated, we recalculate the time out and continue displaying the information. If the warning time or expiration is reached, it fires off over-ridable events that by default use “alert” to display simple messages, but could easily use something like the jQuery UI dialog plugin.
If you’re wondering about the reasoning behind the server time cookie. This was to workaround the differences between the client and server clocks.

Example

In the following code I’ll set the two cookies required for the plugin and use it against two different elements. One for displaying the time, another to show a progress bar (using jQuery UI, not required for the plugin). I also override the onWarning & onExpire events for the progress bar since the user wouldn’t like to be double prompted

Add Jquery And Jquery UI

  <link href="../App_Themes/TestTheme/jquery-ui-1.8.2.custom.css" rel="stylesheet"
        type="text/css" />
    <link href="../App_Themes/TestTheme/stylemain.css" rel="stylesheet" type="text/css" />
    <script src="../JS/jquery-1.4.2.min.js" type="text/javascript"></script>
    <script src="../JS/jquery-ui-1.8.2.custom.min.js" type="text/javascript"></script>
    <script src="../JS/Timeout.js" type="text/javascript"></script>    

Init Session Timeout And a Dialog Box To Dispaly Alert

          $(document).ready(function() {
            $(".sessions").sessionTimeout();
            $("#Session-TimeOut").dialog({
                resizable: true,
                height: 200,
                autoOpen: false,
                modal: true,
                buttons: {
                    Ok: function() {
                        $(this).dialog('close');
                        __doPostBack('<%= Button1.UniqueID %>', '');
                    }
                }
            });
        });   

default.aspx

   



                Your session is about to Expire.


 






        


                
                    
                
            

add cookie on PageLoad

  HttpCookie appCookie = new HttpCookie("SERVERTIME");
        appCookie.Value = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
        appCookie.Expires = DateTime.Now.AddDays(1);
        appCookie.Path = "/";
        Response.Cookies.Add(appCookie);
        HttpCookie appCookie2 = new HttpCookie("SESSIONTIMEOUT");
        appCookie2.Value = DateTime.Now.AddMinutes(HttpContext.Current.Session.Timeout).ToString("yyyy/MM/dd HH:mm:ss");
        appCookie2.Expires = DateTime.Now.AddDays(1);
        appCookie2.Path = "/";
        Response.Cookies.Add(appCookie2);

Running this bit of JavaScript fires the alert shown below:

Download