Find a column in sql database

If you’re looking to find a specific column in a huge database.

Assume, you need to get the table name which holds a column called “BlogStatus” from the content database of an application.

Found the below query,

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%BlogStatus%’
ORDER BY schema_name, table_name;

SQL Where clause

In Stored Procedures, sometime we need to omit input parameters from Where Clause.

For an instance, If the parameter is null, we should not include the value in Where clause conditional criteria.

In such cases, we can use the query efficiently as below,

 

WHERE

(@StatusCode IS NULL OR [StatusCode] like ‘%’+ @StatusCode +’%’)
AND (@FromDate IS NULL OR (DATEDIFF(DD, @FromDate, [CreatedOn]) >= 0)

AND(@ToDate IS NULL OR DATEDIFF(DD, @ToDate, [CreatedOn]) <= 0))

Format data in GridView rows at runtime

We can bind a gridview rows at runtime by customizing its row value in serveral ways. For an instance,

If the row has a DateTime value, we can customize its format at runtime.

Similarly, if we need to concatenate any value as “prefix” or “suffix” we can follow the below given snippet.

For your reference.

Columns required in the data source are,

UserID, UserName, CreatedDate

 

<asp:GridView runat=”server” ID=”gridList” AutoGenerateColumns=”false”>
<Columns>

<asp:TemplateField HeaderText = “User”>
<ItemTemplate>
<asp:Label ID=”lblUserID” runat=”server”>
(<%# Eval(“UserID”)%>) <%# Eval(“UserName”)%>
</asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText = “Created Date”>
<ItemTemplate>
<asp:Label ID=”lblUserID” Text=’<%# Eval(“CreatedDate”,”Created On: {0:MM/dd/yyyy}”) %>’ runat=”server”>
</asp:Label>
</ItemTemplate>
</asp:TemplateField>

</Columns>
</asp:GridView>

 

 

Handle Browser close event

Have read this wonderful article to handle events before the browser being closed by the user.

Ref: http://eureka.ykyuen.info/2011/02/22/jquery-javascript-capture-the-browser-or-tab-closed-event/

Given the code as below,

 

/**
* This javascript file checks for the brower/browser tab action.

*/
var validNavigation = false;

function wireUpEvents() {
/**
* For a list of events that triggers onbeforeunload on IE
* check http://msdn.microsoft.com/en-us/library/ms536907(VS.85).aspx
*
* onbeforeunload for IE and chrome
* check http://stackoverflow.com/questions/1802930/setting-onbeforeunload-on-body-element-in-chrome-and-ie-using-jquery
*/
var dont_confirm_leave = 0; //set dont_confirm_leave to 1 when you want the user to be able to leave withou confirmation
var leave_message = ‘You sure you want to leave?’
function goodbye(e) {
if (!validNavigation) {
if (dont_confirm_leave!==1) {
if(!e) e = window.event;
//e.cancelBubble is supported by IE – this will kill the bubbling process.
e.cancelBubble = true;
e.returnValue = leave_message;
//e.stopPropagation works in Firefox.
if (e.stopPropagation) {
e.stopPropagation();
e.preventDefault();
}
//return works for Chrome and Safari
return leave_message;
}
}
}
window.onbeforeunload=goodbye;

// Attach the event keypress to exclude the F5 refresh
$(‘document’).bind(‘keypress’, function(e) {
if (e.keyCode == 116){
validNavigation = true;
}
});

// Attach the event click for all links in the page
$(“a”).bind(“click”, function() {
validNavigation = true;
});

// Attach the event submit for all forms in the page
$(“form”).bind(“submit”, function() {
validNavigation = true;
});

// Attach the event click for all inputs in the page
$(“input[type=submit]“).bind(“click”, function() {
validNavigation = true;
});

}

// Wire up the events as soon as the DOM tree is ready
$(document).ready(function() {
wireUpEvents();
});

 

 

Group Validation using Jquery

To validate more than one control using same Reg Ex, I have tried as follows.

It may be simple. But i have tried this for Starting up validation methods using Jquery.

index.html

<style type=”text/css”>

td{
height:50px;
vertical-align : top ;
}
   
.inputError{
background-color: #FFFFD5;
border: 2px solid red;
color: red;
}
   
.normaltext{
background-color:white;
color : black ;
border: black 1px solid;
}
.errorMessage{
color: red;
display: block;
font-family: tahoma;
font-size: 12px;
height: 10px;
margin-top: 4px;
}

</style>

<table cellspacing=”5″ cellpadding=”5″>

<tr>

    <td> Text Only</td><td>

    <input name=”textonly” type=”text”>
    <span class=”errorMessage”></span>
    </td>

    </tr>

    <tr><td>Text Only 2</td><td>

    <input name=”textonly” type=”text”>
    <span  class=”errorMessage”></span>
    </td>

    </tr>

    <tr><td>Number Only</td>

    <td><input name=”numberonly” type=”text”>
    <span  class=”errorMessage”></span></td>
   
    </tr>

</table>

Script:

<script type=”text/javascript”>

$(document).ready(function(){

 var textonlyErrorMessage =”Name should only contain text”;

var numberonlyErrorMessage = “Numeric characters only allowed”;

$(“input:text”).addClass(“normaltext”);

    $(“input:text[name='textonly']“).keyup(function(){

    if(this.value.match(/^\s*[a-zA-Z]+\s*$/) == null && this.value.length>0)

    {
       $(this).removeClass(“normaltext”).addClass(“inputError”);
      $(this).parent().find(“.errorMessage”).html(textonlyErrorMessage);
    }

    else

    {

    $(this).addClass(“normaltext”);
    $(this).parent().find(“.errorMessage”).html(“”);
    }

    })

    $(“input:text[name='numberonly']“).keyup(function() {

    if(this.value.match(/^\d*[0-9]+\d*$/) == null && this.value.length>0){

    $(this).removeClass(“normaltext”).addClass(“inputError”);
  
    $(this).parent().find(“.errorMessage”).html(numberonlyErrorMessage);
    }

    else

    {

    $(this).addClass(“normaltext”);
    $(this).parent().find(“.errorMessage”).html(“”);
    }

    })

});

</script>

As given above, This code will group the controls to validate based on its Reg EX. Here i have used “Name” attribute as group identifier.

Note: Remember to include Jquery API Library in script src attribute to run these things without error. ( https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js )

Sorting an XML based on Element using Linq

Have given a simple example to sort an XDocument based on XElement in C# using Linq.

Problem: Given an XML which holds Player Name and Score details. We need to display the Top 5 Players based on their scores.

  1.  Assuming that, we are passing an input XML as string. 
  2. XDocument supports Stream/XML Path.
  3. So we’re converting the String –> Byte Array –> Stream –> XDocument as follows,

byte[] byteArray = Encoding.ASCII.GetBytes(“<SCORES>”+ 

“<ENTRY><NAME>RAT</NAME><SCORE>30</SCORE></ENTRY>”+ 

“<ENTRY><NAME>RABBIT</NAME><SCORE>45</SCORE></ENTRY>”+ 

“<ENTRY><NAME>SWORD</NAME><SCORE>38</SCORE></ENTRY>”+ 

“<ENTRY><NAME>DRAGON</NAME><SCORE>78</SCORE></ENTRY>”+ 

“<ENTRY><NAME>DEER</NAME><SCORE>48</SCORE></ENTRY>”+ 

“<ENTRY><NAME>LION</NAME><SCORE>98</SCORE></ENTRY>”+ 

“<ENTRY><NAME>TIGER</NAME><SCORE>68</SCORE></ENTRY>”+ 

“<ENTRY><NAME>GORILLA</NAME><SCORE>88</SCORE></ENTRY>”+ 

“</SCORES>”);

MemoryStream ms = newMemoryStream(byteArray);

 XDocument xDocument = XDocument.Load(ms);

 var orderedScores = xDocument.Descendants(“ENTRY”)

.OrderByDescending(s => (int)s.Element(“SCORE”)).Take(5);

 

foreach (var orderedScore in orderedScores)

{

 Console.WriteLine(orderedScore.Element(“NAME”).Value + ” –> “

 + orderedScore.Element(“SCORE”).Value);

}

 

Finding Leap Year in SQL in a different method

Usually to know whether the given year is Leap/Not a leap we will divide the given year by 4.

If the remainder is 0 its Leap. Otherwise it is not a leap year.  

But this leads to a wrong result in some scenario. Assume that given year is 2300. It can be divided by 4 with 0 as remainder. But 2300 is not a leap year.

So we can use to the different methods to get the desired result. Given one of the method i have used.

DECLARE @YEAR INT

SET @YEAR = 2300

SELECT @YEAR AS‘YEAR’, CASE

WHEN

DATEDIFF(DAY,

’01/FEB/’+CONVERT(VARCHAR,@YEAR),

’01/MAR/’+CONVERT(VARCHAR,@YEAR))

=29

THEN

‘Is a Leap Year’

ELSE

‘Is not a Leap Year’

END

AS RESULT

In the above example, @year acts as the input parameter which get “Year” as input. For the given Year, i was finding the day difference between Feb 01 and March 01. If it is 29, It is a Leap year Otherwise It is not. It will work under all scenario without any issues.

Tree Structure Join in SQL

Assume we are having a table called EmployeeHierarchy as follows,

We need to display an Employee Hierarchy such as,

If we want to know the downliners under Employee “Mark”, we have “Mike” as one trainee under Mark. So the result should display both Mark and Mike.

Assume we need for “John”. We got Roger who has trainee Jeff who holds Mason.  So we need to display all 4 people in the result set.

All the above details should be taken from only one table. So might be complex to implement. I have Used WITH, UNION ALL clauses to acheive this as follows,

DECLARE @INPUTEMPLOYEE  INT

 SET @INPUTEMPLOYEE = 2

 ;WITH LOGICALCOLLECTION(ID,Employee,TraineeID)AS

(

SELECT EmployeeID ,[Employee Name], [TraineeID] FROM EmployeeHierarchy WHERE [TraineeID] IS NOT NULL  AND EmployeeID = @INPUTEMPLOYEE

 UNION ALL

 SELECT E.EmployeeID,E.[Employee Name] ,E.[TraineeID] FROM EmployeeHierarchy AS E JOIN LOGICALCOLLECTION AS M ON E.EmployeeID = M.TraineeID WHERE E.EmployeeID NOT IN

(SELECT EmployeeID FROM EmployeeHierarchy WHERE TraineeID IS NOT NULL AND EmployeeID = @INPUTEMPLOYEE ) )

SELECT * FROM LOGICALCOLLECTION ORDER BY ID

Which will return the result set as follows,

We can also make this more clear when we use SELF JOIN to show Trainee Name instead of the ID.

Hope its clear.

Admin Queries in SQL

Listed below are queries / stored procedure calls that can be used to get information on Sybase objects such as tables, views, indexes, procedures, triggers, schemas, and users.

Tables and Views

To get all tables, views, and system tables, the following Sybase system stored procedure can be executed.

exec sp_tables ‘%’

To filter by database for tables only, for example master:

exec sp_tables ‘%’, ‘%’, ‘master’, “‘TABLE’”

To filter by database and owner / schema for tables only, for example, master and dbo:

exec sp_tables ‘%’, ‘dbo’, ‘master’, “‘TABLE’”

To return only views, replace “‘TABLE’” with “‘VIEW’”. To return only system tables, replace “‘TABLE’” with “‘SYSTEM TABLE’”.

Owners

This is a query to get all Sybase owners.

select name from dbo.sysusers where uid < 16384 order by name

Procedures

This is a query to get all Sybase procedures.

exec sp_stored_procedures ‘%’

The query can be filtered to return procedures for specific owners and databases by appending more information onto the procedure call, such as the following:

exec sp_stored_procedures ‘%’, ‘dbo’, ‘master’

This can be modified as below to know the SP names and its corresponding text

SELECT OBJSYSOBJECT.NAME,OBJSYSCOMMENTS.[TEXT] FROM SYS.OBJECTS OBJSYSOBJECT

LEFT JOIN SYSCOMMENTS OBJSYSCOMMENTS ON

OBJSYSOBJECT.[OBJECT_ID] = OBJSYSCOMMENTS.[ID]

WHERE OBJSYSOBJECT.[TYPE_DESC] =‘SQL_STORED_PROCEDURE’

Procedure Columns

This is a system stored procedure call to get the columns in a Sybase procedure.

exec sp_sproc_columns ‘get_employee_names’, ‘dbo’, ‘sample’

Functions

Given the custom query to get list of functions used in the respective database along with its script.

SELECT OBJSYSOBJECT.NAME,OBJSYSCOMMENTS.[TEXT] FROM SYS.OBJECTS OBJSYSOBJECT  LEFT JOIN

SYSCOMMENTS OBJSYSCOMMENTS ON

 OBJSYSOBJECT.[OBJECT_ID] = OBJSYSCOMMENTS.[ID]

WHERE OBJSYSOBJECT.[TYPE_DESC] like ‘%function%’

Triggers

This is a query to get all Sybase triggers.

select * from sysobjects where type = ‘TR’

The query can be filtered to return triggers for a specific owner by appending a user_name call onto the where clause to the query.

select * from sysobjects where type = ‘TR’ and user_name(sysobjects.uid) = ‘dbo’

Indexes

This is a query to get Sybase indexes for a particular table. In this example, the table used is employee.

exec sp_helpindex ‘employee’

Cursor In SQL

I have not used CURSOR in my projects till date. So am not aware of the exact usage in which we can use Cursor. Recently I got time to learn CURSOR.

From my understanding, A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.

Will update the exact usage when i get a chance to work on cursor in future. In the meantime, i want to share the example from which i learnt CURSOR. as follows,

DECLARE @OBJCURSOR CURSOR

DECLARE @INTCURSOR INT

SET @OBJCURSOR =CURSOR

FOR

SELECT DISTINCT(EMPLOYEEID)FROM EMPLOYEEPAYHISTORY

OPEN @OBJCURSOR

FETCH FROM @OBJCURSOR INTO @INTCURSOR

 

WHILE (@@FETCH_STATUS=0)

BEGIN

 

PRINT CONVERT(VARCHAR,@INTCURSOR)

 

FETCH FROM @OBJCURSOR INTO @INTCURSOR

 

END

 

CLOSE @OBJCURSOR

DEALLOCATE @OBJCURSOR

 Here i was getting Employee ID into Cursor and looping through each row in the cursor to print the employee ID.

To work with cursors you must use the following SQL statements:

  • DECLARE CURSOR
  • OPEN
  • FETCH
  • CLOSE

 

 

Follow

Get every new post delivered to your Inbox.