sql server script to know when the table accessed last time

If the database has lot many unwanted tables which are not being used by any of the applications and if you want to identify the ununused objects , so that you can remove or move those objects to a separate database , then the system view sys.dm_db_index_usage_stats (sql server 2008) contains the information with when the table accessed last time.

the script SQL-1 will produce the list of the tables which are being used and non-used as well. the records in the column [recent_datetime] contains the list of tables which are non been used since the server started.

Note : the counters on this DMV starts / refresh when the server is restarted.

-- Query-1
with cte_recent as
(
select SCHEMA_NAME(B.schema_id) +'.'+object_name(b.object_id) as tbl_name,
(select MAX(last_user_dt) from (values (last_user_seek),(last_user_scan),(last_user_lookup)) as all_val(last_user_dt)) as access_datetime FROM sys.dm_db_index_usage_stats a
right outer join sys.tables b on a.object_id =  b.object_id
)
select tbl_name,max(access_datetime) as recent_datetime  from cte_recent
group by tbl_name
order by recent_datetime desc , 1



To keep a track of the tables which are non been used , its better to store the results in a separate table and merge the table before restart the sql server or as per your own schedule as the SQL Server installed on cluster then you probably schedule it as a job to wait for an year before making the decision to remove or move those tables to a different database.

create a table TABLES_LAST_ACCESSED with 2 columns as below script

--Query-2
create table TABLES_LAST_ACCESSED (TBL_NAME VARCHAR(256),recent_datetime DATETIME)

sync the table with the script (Query-3) below

-- Query-3
with cte_recent as
(
select SCHEMA_NAME(B.schema_id) +'.'+object_name(b.object_id) as tbl_name,
(select MAX(last_user_dt) from (values (last_user_seek),(last_user_scan),(last_user_lookup)) as all_val(last_user_dt)) as access_datetime FROM sys.dm_db_index_usage_stats a
right outer join sys.tables b on a.object_id =  b.object_id
)
MERGE TABLES_LAST_ACCESSED AS target
USING (select tbl_name,max(access_datetime) as recent_datetime  from cte_recent group by tbl_name) AS source
ON (Target.tbl_name = Source.tbl_name)
when matched then
        UPDATE SET Target.recent_datetime = Source.recent_datetime
when not matched then
        INSERT (tbl_name,recent_datetime)VALUES (source.tbl_name,source.recent_datetime);

query any table by using select , insert , update or delete and execute the below statement. the tables which you accessed recently should be in the top.

SELECT * FROM  TABLES_LAST_ACCESSED ORDER BY 2 DESC

Get column name by comma separator in sql

CREATE function fnColList(@in_vcTbl_name varchar(200))
returns varchar(max)
as
begin
declare @colList2BuildAuditTable  varchar(max)
SELECT @colList2BuildAuditTable = coalesce(@colList2BuildAuditTable+ ',', '')+ '['+ B.NAME +']'
FROM SYSOBJECTS A JOIN SYSCOLUMNS B ON A.ID = B.ID
WHERE A.ID = OBJECT_ID(@in_vcTbl_name)
ORDER BY B.COLORDER

return @colList2BuildAuditTable
end 

Attach and Detach Database

-- You don't want to be in the database you are trying to detach
USE Master
GO

-- Optional step to drop all active connections and roll back their work
ALTER DATABASE DatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- Perform the detach
EXEC sp_detach_db test
GO


CREATE DATABASE CORD_DB_1525
    ON (FILENAME = 'E:\External_HD\DATA\Test.mdf'), -- Main Data File .mdf
    (FILENAME = 'E:\External_HD\DATA\Test_log.ldf'), -- Log file .ldf
     (FILENAME = 'E:\External_HD\DATA\Test.ndf') -- Optional - any secondary data files
    FOR ATTACH
GO

Check a table exists or not in database in SQL

IF OBJECT_ID (N'mytablename', N'U') IS NOT NULL
   SELECT 1 AS val  
ELSE
    SELECT 0 AS val;

Create Table from an existing table in SQL

SELECT * INTO dbo.newtable FROM dbo.oldtable WHERE 1 = 0;

Note that this creates the same column structure (including an IDENTITY column if one exists)
but it does not copy any indexes, constraints, triggers, etc.

If you want to make a copy of the table including all of the data, then leave out the WHERE clause.

SELECT * INTO dbo.newtable FROM dbo.oldtable

Running Total In Gridview Footer In ASP.NET C# VB.NET

In this example i am going to demonstrate how to Display Running Total In GridView Footer Row In ASP.NET using C# and VB.NET. This method works with paging enabled gridview as well.

For demo purpose gridview is populated using sqldatasource having table with columns ID ,Name,Amount

I m showing total of amount column is gridview footer. for this we need to sum the the column in RowDataBound Even of Gridiew 

Html source of gridview is

<asp:GridView ID="GridView1" runat="server" 
              AutoGenerateColumns="False"
              DataKeyNames="ID" DataSourceID="SqlDataSource1" 
              OnRowDataBound="GridView1_RowDataBound" 
              ShowFooter="True" AllowPaging="True" PageSize="5" 
              BackColor="#ffffff" BorderColor="AliceBlue" 
              BorderStyle="None" BorderWidth="1px" 
              CellPadding="3" 
              CellSpacing="2" FooterStyle-BackColor="#da821e" 
              FooterStyle-ForeColor="#ffffff" 
              RowStyle-BackColor="#003366" 
              RowStyle-ForeColor="#ffffff" 
              AlternatingRowStyle-BackColor="#da821e">
<Columns>
     <asp:BoundField DataField="ID" HeaderText="ID" 
                     InsertVisible="False" ReadOnly="True"
                     SortExpression="ID" />
     <asp:BoundField DataField="Name" HeaderText="Name" 
                     InsertVisible="False" ReadOnly="True"
                     SortExpression="Name" FooterText="Total"/>
     <asp:TemplateField HeaderText="Amount">
     <ItemTemplate>
     <asp:Label ID="lblAmount" runat="server" 
                Text='<%# "$"+Eval("Amount").ToString()%>'>
     </asp:Label>
     </ItemTemplate>
     <FooterTemplate>
     <asp:Label ID="lblTotal" runat="server"></asp:Label>
     </FooterTemplate>
     </asp:TemplateField>
     </Columns>
     <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
     <HeaderStyle BackColor="#da821e" Font-Bold="True" 
                  ForeColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [ID], [Name], [Amount] FROM [Expenses]">
</asp:SqlDataSource>

Now we need to write code for summing the column in RowdataBound Even of GridView

C# code behind

01public partial class _Default : System.Web.UI.Page

02{

03    decimal grdTotal = 0;

04    protected void Page_Load(object sender, EventArgs e)

05    {

06 

07    }

08protected void GridView1_RowDataBound

09                   (object sender, GridViewRowEventArgs e)

10{

11 if (e.Row.RowType == DataControlRowType.DataRow)

12 {

13  decimal rowTotal = Convert.ToDecimal

14              (DataBinder.Eval(e.Row.DataItem, "Amount"));

15  grdTotal = grdTotal + rowTotal;

16 }

17 if (e.Row.RowType == DataControlRowType.Footer)

18 {

19  Label lbl = (Label)e.Row.FindControl("lblTotal");

20  lbl.Text = grdTotal.ToString("c");

21 }

22}

23}

VB.NET code behind

01Public Partial Class _Default

02    Inherits System.Web.UI.Page

03    Private grdTotal As Decimal = 0

04    Protected Sub Page_Load

05    (ByVal sender As Object, ByVal e As EventArgs)

06 

07End Sub

08 

09Protected Sub GridView1_RowDataBound

10(ByVal sender As Object, ByVal e As GridViewRowEventArgs)

11 

12If e.Row.RowType = DataControlRowType.DataRow Then

13Dim rowTotal As Decimal =

14Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Amount"))

15grdTotal = grdTotal + rowTotal

16End If

17 

18If e.Row.RowType = DataControlRowType.Footer Then

19Dim lbl As Label = DirectCast(e.Row.FindControl

20                           ("lblTotal"), Label)

21lbl.Text = grdTotal.ToString("c")

22End If

23End Sub

24End Class