How I find a particular column name within all tables of SQL server datbase.

For example, I have a database named Organisation. I have more than one table where tax_id column is present.

Most of the time, we have to find such a column from the whole database. The solution is provided below:
select table_name,column_name from information_schema.columns where column_name like ‘%tax%’

 
Here I am searching for column_name which contains tax within its name. It will return all the tables and respective columns containing tax.

Example:

Table_Name Column_name
t_Customer tax_id
t_Employee tax_number
t_Balance_sheet tax_percentage

 

Advertisements

ASP.Net GridView – Thousand comma seperator for Currency Amounts

To add a thousand comma seperator in your GridView in ASP.Net application, use the following syntax:

<%#string.Format(“{0:#,##0.00}”, Eval(“Fees”))%>

No need to convert it to decimal or use System.Globalization crap.

Hope this helps someone.

Easy way to add Serial Numbers Column in Gridview

May occasions we need to give Serial numbers to our grid like 1,2,3,4,5, this I have tried my hand for a while and what I found…..

 

<asp:TemplateField>
            <HeaderTemplate>
            Serial No.</HeaderTemplate>
            <ItemTemplate>
            <asp:Label ID="lblSRNO" runat="server" 
                Text='<%#Container.DataItemIndex+1 %>'></asp:Label>
            </ItemTemplate>
            </asp:TemplateField>

This code works as same when we apply paging.

We cannot use this on BoundFields because they support only those objects who have Databinding event

Get Full Month

ALTER FUNCTION [dbo].[getFullmonth]
(
@date1 date
)
RETURNS @dates TABLE
(
date date not null
)
AS
BEGIN
-- Set first day in month
DECLARE @month int;

SET @month = datepart(MM, @date1);
SET @date1 = convert(datetime, convert(varchar,datepart(yy,@date1)) + ‘.’ + convert(varchar,@month) + ‘.01 00:00:00’);
WHILE datepart(MM,@date1) = @month
BEGIN
INSERT INTO @dates VALUES (@date1);
SET @date1 = dateadd(dd, 1, @date1);
END
RETURN;
END