SQL Get List of Tables and SPs and Views Used/NotUsed of Database

 -- used XYZ DB views into PQR DB SPs


SELECT DISTINCT




--SP_Name = O.name,


Table_Name = OO.name

FROM sys.sysdepends D INNER JOIN sys.sysobjects O ON

O.id = D.id

INNER JOIN sys.sysobjects OO ON

OO.id = D.depid

WHERE O.xtype = 'P' And OO.xtype = 'V'



　

---- count of used sps in db



WITH T1




as


(


select SPECIFIC_NAME

from XYZ.information_schema.routines

where routine_type = 'PROCEDURE'

)

,



T2

as

(

SELECT o.name as SPECIFIC_NAME

FROM sys.dm_exec_procedure_stats ps

INNER JOIN

sys.objects o

ON ps.object_id = o.object_id

WHERE DB_NAME(ps.database_id) = 'XYZ' And O.type = 'P'

)

select *,COUNT (T1.SPECIFIC_NAME) OVER (PARTITION BY T1.SPECIFIC_NAME) from T1

LEFT JOIN T2 ON T1.SPECIFIC_NAME = T2.SPECIFIC_NAME





---- not used sp by execution time


select SPECIFIC_NAME

from XYZ.information_schema.routines

where routine_type = 'PROCEDURE'

And SPECIFIC_NAME not in




(


SELECT o.name as SPECIFIC_NAME

FROM sys.dm_exec_procedure_stats ps

INNER JOIN

sys.objects o

ON ps.object_id = o.object_id

WHERE DB_NAME(ps.database_id) = 'XYZ' And O.type = 'P'




)



----used sp by execution time

SELECT o.name,

ps.last_execution_time

FROM sys.dm_exec_procedure_stats ps

INNER JOIN

sys.objects o

ON ps.object_id = o.object_id

WHERE DB_NAME(ps.database_id) = 'XYZ' And O.type = 'P'

ORDER BY

ps.last_execution_time DESC




---- get name of sps


select *

from XYZ.information_schema.routines

where routine_type = 'PROCEDURE'



　

-- not used tables


SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='PQR'

And TABLE_NAME not in

(SELECT DISTINCT

Table_Name = OO.name

FROM sys.sysdepends D INNER JOIN sys.sysobjects O ON

O.id = D.id

INNER JOIN sys.sysobjects OO ON

OO.id = D.depid

WHERE O.xtype = 'P')




-- used tables


SELECT DISTINCT

SP_Name = O.name,

Table_Name = OO.name

FROM sys.sysdepends D INNER JOIN sys.sysobjects O ON

O.id = D.id

INNER JOIN sys.sysobjects OO ON

OO.id = D.depid

WHERE O.xtype = 'P' And OO.xtype = 'U'
 

how to find entire links of a website C#.net

Here's an example straight from their examples page on how to find all the links in a page

I'd look at using the Html Agility Pack.

 HtmlWeb hw = new HtmlWeb();
 HtmlDocument doc = hw.Load(/* url */);
 foreach(HtmlNode link in doc.DocumentElement.SelectNodes("//a[@href]"))
 {

 }

uncaught typeerror cannot read property 'settings' of undefined jquery validate

I am getting error on line # 1069 where I write the below code and i'm done

if (!validator) return;

 

just need to write the above line in jquery.validator.js file line # 1069

 

Ref. Site

https://devio.wordpress.com/2012/07/18/fixing-jquery-validation-with-changing-validators/

Clearing the credentials for connecting to a Team Foundation Server

i have one issue with team foundation server(TFS), I logged in with xyz and then I tried to login with abc, so again I required to login with xyz and I am getting stuck doing that ...

Ran in to a situation a couple of days ago where i needed to log into a Team Foundation Server as a different user - and since Visual Studio/Team Explorer "remembers" your last credentials you don't have a chance to re-enter these credentials.  This information is cached even after removing and re-adding the server in team explorer.  Which begs the question - where is it cached?

Turns out we are relying on Windows to do this for us.  To dump this cache all you need to do is go to control panel > User Accounts > Manage Your Network Passwords select the Team foundation Server and choose remove - viola! Next time you go into Team Explorer you will be prompted for a new set of credentials. 

And i'm done with my problem ....

Ref. site
http://blogs.msdn.com/b/visualstudioalm/archive/2012/08/29/clearing-the-credentials-for-connecting-to-a-team-foundation-server.aspx
 

mvc / mvc 5 bootstrap-paginator customized pagination

@{
    Layout = null;
}

    $(function () {
        var options = {
            totalPages: '@ViewBag.TotalPages',
            size: 'large',
            alignment: 'right',
            onPageClicked: function (e, originalEvent, type, page) {
                ObjParams.pageNumber = page;
                ;
                $.ajax({
                    url: '@Url.Action(null)',
                    type: 'POST',
                    dataType: 'json',
                    data: ObjParams,
                    success: function (response) {
                        ;
                        $("#divContainer").html('');
                        $("#divContainer").empty();
                        $("#divContainer").html(response.DataContent.Content);
                    },
                    error: function (response) {
                    }
                })
            }
        }
        $('#paginator-test').bootstrapPaginator(options);
    });

c#.net mathematical expression string to calculation

c#.net mathematical expression string to calculation

If you want to evaluate a string expression use the below code snippet.

using System.Data;

DataTable dt = new DataTable();
var v = dt.Compute("3 * (2+4)","");

SQL Dependency with Cache (server side) For MVC and ASP.NET

Step 1 :

ALTER DATABASE [XYZ] SET ENABLE_BROKER with rollback immediate;
ALTER DATABASE [XYZ] SET TRUSTWORTHY ON;

Step 2 :


   
     
       
         
       
     
   

Step 3 :

public DataSet GetReaderByCmdCaching(string command, int keyID)
        {
            object classlock = new object();
            this.GetConnection();
            System.Data.DataSet returnValue = new DataSet();
            string strKey = "readerdata" + keyID;
            string strKeyNavigation = "readerdatanavigation" + keyID;
            if (HttpContext.Current.Cache[strKey] == null)
            {
                lock (classlock)
                {
                    System.Data.SqlClient.SqlDependency.Start(this.connectionString);
                    this.sqlCommand.CommandText = command;
                    SqlCacheDependency dependency = new SqlCacheDependency("CacheDatabaseConnection", "TableName");
                    SqlDataAdapter da = new SqlDataAdapter(this.sqlCommand);
                    da.Fill(returnValue);
                    HttpContext.Current.Cache.Insert(strKey, returnValue, dependency);
                }
            }
            else
            {
                returnValue = (System.Data.DataSet)HttpContext.Current.Cache[strKey];
            }
           
            return returnValue;
        }

Step 4 :

DataSet dsTemp = GetReaderByCmdCaching("Name of SP or Query", UniqueID);

Step 5 :

You are done, whenever any data change in Table it generate the Cache :) Happy Coding.