Modified Bigtables.sql

Nov 26, 2001

Here's an updated version of bigtables.sql that also displays the ratio of index size to data size and the percentage of unused space per table. I've found the index to data ratio particularly helpful for finding and fixing over-indexing.

Cheers
-b

/**************************************************************************************
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@sqlteam.com
* v1.11
*
**************************************************************************************/

declare @idint
declare @typecharacter(2)
declare@pagesint
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpagedec(15,0)
declare @pagesperMBdec(15,0)


create table #spt_space
(
objidint null,
rowsint null,
reserveddec(15) null,
datadec(15) null,
indexpdec(15) null,
unuseddec(15) null
)

set nocount on

-- Create a cursor to loop through the user tables
declare c_tables cursor for
selectid
fromsysobjects
wherextype = 'U'

open c_tables

fetch next from c_tables
into @id

while @@fetch_status = 0
begin

/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id

select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id


/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id

update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id

fetch next from c_tables
into @id
end


select top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB'),
idx_data_ratio = ltrim(str(indexp*100 /data) + '%'),
unused_pct = ltrim(str(unused * 100 /reserved) + '%')

from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc

drop table #spt_space
close c_tables
deallocate c_tables

Edited by graz to add code tags around the code.

View 9 Replies


ADVERTISEMENT

Last Modified Row

May 29, 2001

Is there any way for me to return the row that was last entered? If so, how do I go about accomplishing this?

For example,

Column1-Plant NameColumn2-Creation DateColumn3-Comments
Bayport 12305/24/01 2:51 AMAirflow became unstable
Bayport 12305/24/01 4:00 AM
Bayport 12305/24/01 5:36 AMNo events
Bayport 12305/24/01 1:00 PM
Bayport 12305/26/01 2:45 PMNo events
Bayport 12305/26/01 3:12 PMStarted liquifier 25% LIN
Bayport 405/24/01 2:51 AMSwung liquifier 0% to LIN
Bayport 405/26/01 5:45 AM
Bayport 405/26/01 5:15 PMLiquifuer @ 25% LIN
Coatesville05/24/01 9:32 AM
Coatesville05/26/01 4:25 PMNo events

If I were to query against 5/26, I would want my result to return as:
Bayport 123 5/26/01 3:12 PM (because this was the last row entered on the 26th) Started liquifier 25% LIN.
Bayport 4 5/26/01 5:15 PM (because this was the last row entered on the 26th) Liquifier @ 35% LIN.
Coatesville 5/26/01 4:25 PM No events.

View 1 Replies View Related

Modified Date

Apr 16, 2001

I am taking a "complete backup" of my production db every day using Backupagent of Arcserver 2000. But the Modified Date of .mdf and .ldf files show an older date. Is it normal?
Thanks
Wilson

View 1 Replies View Related

SP Last Modified DateTime

Mar 21, 2002

Does anyone know how to get stored procedure or for that matter any other object last modified time?

View 1 Replies View Related

Table Modified

Jan 14, 2004

Hi all,
Sql server 7

Is there anyway to findout if list of tables that has been modified after august 2003 in a database.

Waiting for ur reply.

TIA
Adil

View 3 Replies View Related

How 2 Find Modified Sps

Jul 26, 2007

dear friends,

iam new to ssql server.i want to find the modified date of stored procedures. can any one help me in this

View 4 Replies View Related

Modified Date

Feb 21, 2008

Is there a way to see the last modified date on the Table structure or a stored Procedure in SQL Server 2000?

Thx

View 2 Replies View Related

How To Know What Schema Was Modified

Jul 20, 2005

Hello,Is there a way in MSSQL server to find all the objects in the databasebased on the modified date rather than the created date.Thanks in advanceKumu

View 1 Replies View Related

Top10 + Modified Records

Dec 18, 2007

 I have stored procedure to show 10 new record from table:SELECT     TOP 10 *FROM         tableWHERE     (id = @id)ORDER BY id DESC and work fine.I'd like to show 10 new record and include modified record as new record. Wo to do that?

View 5 Replies View Related

Table&#39;s Last Modified Date

Jan 9, 2001

Does anyone know how to get a table's 'last modified date' in SQL 7 ?
Sysobjects contains the 'create date', but I can't find a 'last modified date' anywhere......

View 1 Replies View Related

Getting Number Of Records Modified

Oct 20, 2004

Hi all

My manager ask me to provide him with the total number of records which have been added, deleted or modified on a certain database in SQL Server 2000 during the month of September. Is there away to get that information from the transaction log or by any how?

please some one guide me how to do that?

Your help is highly appreciated

View 8 Replies View Related

Finding Modified Objects

Apr 18, 2005

hi,
I want to find the modified database objects(Tables, Proc and Fun) in last 3 days in SQL 2000.

With Regards
Praveen (DBA)

View 4 Replies View Related

SELECT Last Modified Data

Sep 24, 2004

Hi,

I have data that sometimes will be modify. when modify the new data will be insert and the old data still remain inside the database. The same item have their own unique id. So I want to query the data that last modified. How?



Thanks

View 8 Replies View Related

IDs For Insert And Modified Fields

Jan 21, 2015

if you all add ID's with your Inserts and Modified (Date) fields. Such as 1 would indicate if the record was inserted manually, whereas 2 might indicate it was update or inserted by a web application.

View 2 Replies View Related

What Should Be Modified In This Stored Procedure

Apr 13, 2008

Hi,

I want to retrieve the values from store_geofence table where the timeduration exceeds certain interval

the procedure is as below
CREATE
procedure [dbo].[GeofenceByCustomer]
@regno nvarchar(50),
@frmdate nvarchar(50),
@todate nvarchar(50),
@Geofence nvarchar(50),
@interval int,
@userid nvarchar(50)
as
begin
declare @val int
Declare @strSql as nvarchar(3000)
Select @val=count(*) from basestation where superuserid=@userid and
base_station_name=@Geofence

--if(@val=0)
begin
create table #templ(registrationno nvarchar(50),basestation
nvarchar(50),entry_time datetime,exit_time datetime,duration
int)
select @strSql='
insert into #templ(registrationno,basestation,entry_time,exit_time,duration)
select
registrationno,basestation,entry_time,exit_time,datediff(mi,entry_time,exit_time)
as duration from store_geofence where entry_time>'+@frmdate+' and
exit_time<'+@todate+' and datediff(mi,entry_time,exit_time)>'+@interval+'
and basestation in ('+@Geofence+') order by entry_time,registrationno'
execute (@strSql)
print @strSql
select * from #templ
drop table #templ
end

end
GO

I should specify the duration in int to take affect

datediff(mi,entry_time,exit_time)>'+@interval+'


but i do not know how to rewrite this path
i cannot put as
datediff(mi,entry_time,exit_time)>'+@interval+'


it returns an error
Syntax error converting the nvarchar value '
insert into #templ(registrationno,basestation,entry_time,exit_time,duration)
select
registrationno,basestation,entry_time,exit_time,datediff(mi,entry_time,exit_time)
as duration from store_geofence where entry_time>01/01/2008 and
exit_time<01/01/2008 and datediff(mi,entry_time,exit_time)>' to a column of data type int.



How should i rewrite it

please help

regards
cmrhema

View 2 Replies View Related

How To Get Modified Column Name In Tables

Sep 4, 2013

If we want to get the changes happened in database we will get through sys.objects or sys.tables .but we are only getting it table name . But how to get by table name and what the column added to that table or what column has been modified (datatype or constraint) .

For example if i added employee table column called deptid it should be shown in changes in geiven date

SELECT
modify_date
,type_desc
,name
FROM sys.objects
WHERE is_ms_shipped=0
--AND modify_date>='yyyy/mm/dd' <--optionally put in your date here
ORDER BY 1 DESC

How to get column name of a table which is modified...

View 1 Replies View Related

Record Datetime Of Last Modified

Apr 16, 2007

I have a datetime field in a table want to use to record the date time last the row/field of the row is modified.

Can I do this in SQL Server 2005 without writing any code/trigger?

Thanks
J.

View 2 Replies View Related

Get File Modified Date

Jan 10, 2008

Hi,

the ssis package loops through a folder using a foreach loop container and imports the data inside each .csv file into the database.

Now I would like to add the functionality to first check the modified date of the .csv file. If it is NOT today's date then the package should fail or go to error.

Any thoughts how to do this please?

Thanks

View 5 Replies View Related

How To Get Modified Time Of A Table

Jul 11, 2006

Hello All,I would like to get last modified timestamp for a table. Is there any way toget that information using any commands?Thanks in Advance,Muthu.

View 2 Replies View Related

Last Modified Date Of A View

Jul 20, 2005

How to get the date a view was last modified? (As opposed to created)

View 2 Replies View Related

Get Modified Date Of File

Jan 10, 2008

Hi,
the ssis package loops through a folder using a foreach loop container and imports the data inside each .csv file into the database.
Now I would like to add the functionality to first check the modified date of the .csv file. If it is NOT today's date then the package should fail or go to error.
Any thoughts how to do this please?
Thanks

View 1 Replies View Related

File And Date Modified

Jul 20, 2007

Hello all,

I was just thinking about a situation which I have been tasked with in that I have a package that is scheduled to run once a day which will import data from a flat file. The data does not have any distinguishing time that can be used to see whether or not it is beyond a last imported time.



I was wondering, is there a way to access the date modified of the file? If so I could simply append this time to a table which keeps track of all of the files and the modified dates which I have ever imported for this task.

View 5 Replies View Related

Last Modified To Store Procedure

Feb 27, 2006

Hello everyone,

I have store procedure that was created on 9/15/2005. It went through some modificiations since then. How can I find out when was the last updated or last two update on this object?

Thanks

View 3 Replies View Related

Last Modified Date Of The Table

Dec 27, 2007

Hi,

How to find out the Last Modified (Structure) in the SQL Table?. Is there any query please let me know.

Regards,
S.Balavenkatesh

View 3 Replies View Related

Datestamping A Modified Record...

Feb 22, 2008



I have a simple SQL2005 database with a single table that I need to be able stamp a record in a "modifiedon" field to represent when it was modified. It's set to a datetime format, but am unsure how to proceed next.

Any help would be appreciated.

- Pete

View 6 Replies View Related

List Of Modified Table ?

Jan 7, 2008

Hi Everybody,

i am it sutck when i am writing this sql in sqlserver2005

my requirement is to list all the table name and along with their filed name if some one modified (table definition ) for a specifc date range ?

is there any way to get these information in sql server 2005 ?



regards

sujithf

View 3 Replies View Related

SQL Script To Find When Table Last Modified

Nov 8, 2007

 I am looking for two sql scripts,1. How can i check when a tables structure was last modified? 2. How can i find the structure differences between two databases that were created the same but changed over time individualy?Looking for sql scripts to do this. Any help on either would be great.Thanks 

View 2 Replies View Related

Restoring Rows Accidentaly Modified

Oct 19, 2005

Hello!How can I Restore rows which are accidentaly modified.I forgetted to put where condition in "Update" Query.Now I want my data back. I have not taken back up manually yet.I am using SQL Server 2000.Please help.Thank you,

View 3 Replies View Related

How To Get DateBase Table Last Modified Date ?

Dec 26, 2005

Hi

View 9 Replies View Related

How To Show Last Time TABLE Was MODIFIED?

May 15, 2000

Hi all,

What is the best way to show when a table was last MODIFIED?

Thanks,
Stan

View 1 Replies View Related

Checking For Modified Dates For Tables

Jul 11, 2003

In the olden days when I used databases that kept each table in a separate file, I used to be able to tell when a specific table was modified by the date stamp on the file.

Is there a method in SQL server to check the "last modifed date" at the table level?

I didn't see anything in sysobjects.

View 5 Replies View Related

Indexes Update When Table Is Modified

Jun 8, 2004

Hello again,

Two Short questions this time,

I have a table with several indexes, currently most of them are very narrow (one column), and the question is, when I modify the table by updating a record, does all the indexes are calculated again?? Even if the modified field isn't indexed??? Or the server is smart and knows what indexes to calculate if any.

Second question, can I give to a query a low priority(In dynamic SQL), for example when I don't want my query to exploit too many system resources so it won't interfere the main system ?


Inon.

View 12 Replies View Related

Win Date Modified Field On MDF Files

Jan 13, 2004

I'm an Oracle DBA and just getting used to MS Sqlserver. I noticed that the windows explorer "date modified" field for my database files ( .MDF files ) doesn't change much even though there is activity going on. Sometimes it doesn't change for a week.

Is this the expected behavior? Could it be that no data is changing in my database? ( I find that hard to believe)

Thanks for any insights.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved