list table name and schema using sql

Just a hours ago, i was looking for code which will list-out all the tables in database with its schema name. I found so many way to do this , but here i found simple and easy way to implement it.

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
 AS ListofTables_withSchema
FROM sys.tables

This will returns all the List of table along with the schema names.

 

 

 

 

 Happy coding...

Gridview header sorting using C#

In my previous post i explained, how to Search string pattern using sql on SQL i had explained how to Add Image in between Rows of a GridView using C#.

 

Now in this article i will explain one of the useful feature i.e Gridview Sorting.
you need to set the AllowSorting property as a True. and SortExpression Property of columns to the specific field name from the database.
Lets look at the below sample gridview code.

 

<asp:GridView ID="gvDetails" runat="server"  onsorting="gvDetails_Sorting" AllowSorting="True">
<Columns>
<asp:TemplateField HeaderText="Your Name" SortExpression="FirstName">
<ItemTemplate>
<%#Eval("YourName")%>'/>
</ItemTemplate>
</asp:TemplateField>
</asp:GridView>

Now for sorting you need to create one public property which store the value of direction in the viewstate and base on that select query get fired.

public GetSortDir direction
{
  get
  {
    if (ViewState["SortingDir"] == null)
        {
            ViewState["SortingDir"] = GetSortDir.Ascending;
         }
         return (GetSortDir)ViewState["SortingDir"];
   }
   set
   {
       ViewState["SortingDir"] = value;
   }
}

Now check the gridview directon and base on viewstate it will get set the new direction.

    protected void gvDetails_Sorting(object sender, GridViewSortEventArgs e)
    {
        string sortDirection = string.Empty;
        if (direction == sortDirection.Ascending)
        {
            direction = sortDirection.Descending;
            sortDirection = "Desc";
        }
        else
        {
            direction = sortDirection.Ascending;
            sortDirection = "Asc";
        }
       
        DataView sortedView = new DataView(BindGridView());
        sortedView.Sort = e.SortExpression + " " + sortDirection;
        gvDetails.DataSource = sortedView;
        gvDetails.DataBind();
    }

Here BindGridView() is the function which returns the datatable & it contain the query result.

that's it now check the gridview and click on the header and see the sorting effects get added in your gridview.

 

  

Parsing a delimited string in SQL

 Most of the time we required the delimited strings to be added in database. today i come up with some simple solution. which will read the string and specified delimited character and base on that it will divide the string and split it across.

CREATE FUNCTION ParseValues

(@String varchar(8000), @Delimiter varchar(10) )

RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))

AS

BEGIN

DECLARE @Value varchar(100)

WHILE @String is not null

BEGIN

SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END

INSERT INTO @RESULTS (Val)

SELECT @Value

END

RETURN

END

You can call this function in your query and it will return the result.(as show in img)

select * from dbo.ParseValues('This;is;a;delimited;string;value',';')

and here is your result..

   
 Hope this will helps you, Please put your comments or dought to help others.
 

Enabling Database Mail via TSQL


-- allow advanced options

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

-- enable database mail (0 disables , 1 enables)

sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

-- hide advanced options again

sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO



sql:Limit SQL Server memory

on SQL you can Set memory available to a sql instance
on below example we are setting Limit to the 500MB


sp_configure 'show advanced options',1
go
reconfigure
go

sp_configure 'max server memory', 500
go
reconfigure
go



like this way you can set the limit server memory, but for precaution you can try this on local machine without running diretly on live server.