How to force DB shrink?

I have DB 13GB in size. After we deleted some old tables
from it it's still using 13GB on hard drive. In properties
it was always set up "Auto shrink", but after a week it's
still 13GB... :(
How to force shrinking manual?Try running DBCC OPENTRANS to see if you have any long running open
transactions preventing the SHRINK.
Also have a look at DBCC SHRINKFILE in BOL
HTH
Ryan Waight, MCDBA, MCSE
"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> I have DB 13GB in size. After we deleted some old tables
> from it it's still using 13GB on hard drive. In properties
> it was always set up "Auto shrink", but after a week it's
> still 13GB... :(
> How to force shrinking manual?
>|||Is it data or log file which is big? If log, check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> I have DB 13GB in size. After we deleted some old tables
> from it it's still using 13GB on hard drive. In properties
> it was always set up "Auto shrink", but after a week it's
> still 13GB... :(
> How to force shrinking manual?
>|||If you are trying to shrink the data file then you can use DBCC SHRINKFILE.
You will be unable to shrink the data file if there are allocated extents at
the end. You can move indexes and tables with clustered indexes by doing a
DBCC DBREINDEX. If the extents are from a table with non clustered index or
text/image data, then you have more of an issue. Unfortunately these do not
move with the DBCC DBREINDEX command. You will have to create a new table
and copy the data back in to physically move the extents. A better option
is to create a new file group for your non clustered tables and text/image
data and move it there.
To find the offending tables and indexes download the free tool below.
Select from the tool bar Table/Index View. This will show you a list of
tables and indexes and where they are allocated in the data file. Scroll to
the end of the data file click on an allocated extent (green). This will
highlight the index that is allocated to the extent. You can then run DBCC
DBREINDEX on that index.
Hope this helps
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> I have DB 13GB in size. After we deleted some old tables
> from it it's still using 13GB on hard drive. In properties
> it was always set up "Auto shrink", but after a week it's
> still 13GB... :(
> How to force shrinking manual?
>|||"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> I have DB 13GB in size. After we deleted some old tables
> from it it's still using 13GB on hard drive. In properties
> it was always set up "Auto shrink", but after a week it's
> still 13GB... :(
> How to force shrinking manual?
>
Autoshrink shrinks a database if it has 25% space in it. If the table took
up less space than that , then you have to manually shrink it
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003|||This is the main question! How manually shrink DB?!
>--Original Message--
>"Alexandre Rybalov" <alex@.covers.com> wrote in message
>news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
>> I have DB 13GB in size. After we deleted some old tables
>> from it it's still using 13GB on hard drive. In
properties
>> it was always set up "Auto shrink", but after a week
it's
>> still 13GB... :(
>> How to force shrinking manual?
>Autoshrink shrinks a database if it has 25% space in it.
If the table took
>up less space than that , then you have to manually
shrink it
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.518 / Virus Database: 316 - Release Date:
11/09/2003
>
>.
>|||Alexandre,
You can use DBCC SHRINKFILE for data files also.If it still doesnt shrink,
check for data fragmentation using DBCC SHOWCONTIG.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:52fe01c37d30$3ab07190$a501280a@.phx.gbl...
> This is the main question! How manually shrink DB?!
> >--Original Message--
> >"Alexandre Rybalov" <alex@.covers.com> wrote in message
> >news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> >> I have DB 13GB in size. After we deleted some old tables
> >> from it it's still using 13GB on hard drive. In
> properties
> >> it was always set up "Auto shrink", but after a week
> it's
> >> still 13GB... :(
> >>
> >> How to force shrinking manual?
> >>
> >Autoshrink shrinks a database if it has 25% space in it.
> If the table took
> >up less space than that , then you have to manually
> shrink it
> >
> >
> >--
> >Outgoing mail is certified Virus Free.
> >Checked by AVG anti-virus system (http://www.grisoft.com).
> >Version: 6.0.518 / Virus Database: 316 - Release Date:
> 11/09/2003
> >
> >
> >.
> >

How to fire a trigger without changing table data

I have tables that I want to fire either an update or insert trigger on.

I could write a script containing a long list of inserts but I'm looking for
something simpler. Would isql work? Any special conditions to get it to
work?

I've tried tricks like 'update x set col = col' or 'update x set col = col +
'' '

All the alternatives seem to have problems. Any ideas?

--== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--Try:

update MyTable
set
Col1 = 'x'
where
1 = 2

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John Smith" <nobody@.nowhere.com> wrote in message
news:1143510748_10105@.sp6iad.superfeed.net...
I have tables that I want to fire either an update or insert trigger on.

I could write a script containing a long list of inserts but I'm looking for
something simpler. Would isql work? Any special conditions to get it to
work?

I've tried tricks like 'update x set col = col' or 'update x set col = col +
'' '

All the alternatives seem to have problems. Any ideas?

--== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:SL0Wf.1028$m35.96044@.news20.bellglobal.com...
> Try:
> update MyTable
> set
> Col1 = 'x'
> where
> 1 = 2

Thanks, but it doesn't work.

--== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||> Thanks, but it doesn't work.

The script Tom posted works for me: Please expand on what you mean by 'it
doesn't work'.

CREATE TABLE MyTable(Col1 int)
GO

CREATE TRIGGER TR_MyTable
ON MyTable FOR INSERT, UPDATE AS
PRINT 'Trigger fired'
GO

UPDATE MyTable
SET Col1 = 'x'
WHERE 1 = 2
GO

DROP TABLE MyTable
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"John Smith" <nobody@.nowhere.com> wrote in message
news:1143512881_10135@.sp6iad.superfeed.net...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:SL0Wf.1028$m35.96044@.news20.bellglobal.com...
>> Try:
>>
>> update MyTable
>> set
>> Col1 = 'x'
>> where
>> 1 = 2
> Thanks, but it doesn't work.
>
> --== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
> News==--
> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:_s1Wf.9738$tN3.2012@.newssvr27.news.prodigy.ne t...
>> Thanks, but it doesn't work.
> The script Tom posted works for me: Please expand on what you mean by 'it
> doesn't work'.

Thanks for the help. The problem was due to NULL values in some columns.

The trigger was firing but not changing data.

--== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||>>The trigger was firing but not changing data.

Thats what your question says

Madhivanan

How to find which tables have been pinned in memory.

Is there a way to find which tables are pinned in memory ?
-Nags
SELECT table_name
FROM information_schema.tables
WHERE OBJECTPROPERTY(OBJECT_ID(table_schema + '.' + table_name),
'TableIsPinned') = 1
Jacco Schalkwijk
SQL Server MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Is there a way to find which tables are pinned in memory ?
> -Nags
>
|||Hello,
Use the property "TableIsPinned" property with Objectproperty function.
1 = True (in memory)
0 = False
usage
select objectproperty('tableid','TableIsPinned)
How to get the table id:-
select object_id('table_name')
Thanks
Hari
MCDBA
"Nags" <nags@.DontSpamMe.com> wrote in message
news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Is there a way to find which tables are pinned in memory ?
> -Nags
>
|||Got it. Thank you
-Nags
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uS71O8JeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> SELECT table_name
> FROM information_schema.tables
> WHERE OBJECTPROPERTY(OBJECT_ID(table_schema + '.' + table_name),
> 'TableIsPinned') = 1
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
>

How to find which tables have been pinned in memory.

Is there a way to find which tables are pinned in memory ?
-NagsSELECT table_name
FROM information_schema.tables
WHERE OBJECTPROPERTY(OBJECT_ID(table_schema + '.' + table_name),
'TableIsPinned') = 1
Jacco Schalkwijk
SQL Server MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Is there a way to find which tables are pinned in memory ?
> -Nags
>|||Hello,
Use the property "TableIsPinned" property with Objectproperty function.
1 = True (in memory)
0 = False
usage
--
select objectproperty('tableid','TableIsPinned)
How to get the table id:-
select object_id('table_name')
Thanks
Hari
MCDBA
"Nags" <nags@.DontSpamMe.com> wrote in message
news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Is there a way to find which tables are pinned in memory ?
> -Nags
>|||Got it. Thank you
-Nags
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uS71O8JeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> SELECT table_name
> FROM information_schema.tables
> WHERE OBJECTPROPERTY(OBJECT_ID(table_schema + '.' + table_name),
> 'TableIsPinned') = 1
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
>

How to find which tables have been pinned in memory.

Is there a way to find which tables are pinned in memory ?
-NagsSELECT table_name
FROM information_schema.tables
WHERE OBJECTPROPERTY(OBJECT_ID(table_schema + '.' + table_name),
'TableIsPinned') = 1
Jacco Schalkwijk
SQL Server MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Is there a way to find which tables are pinned in memory ?
> -Nags
>|||Hello,
Use the property "TableIsPinned" property with Objectproperty function.
1 = True (in memory)
0 = False
usage
--
select objectproperty('tableid','TableIsPinned)
How to get the table id:-
select object_id('table_name')
Thanks
Hari
MCDBA
"Nags" <nags@.DontSpamMe.com> wrote in message
news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Is there a way to find which tables are pinned in memory ?
> -Nags
>|||Got it. Thank you
-Nags
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uS71O8JeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> SELECT table_name
> FROM information_schema.tables
> WHERE OBJECTPROPERTY(OBJECT_ID(table_schema + '.' + table_name),
> 'TableIsPinned') = 1
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > Is there a way to find which tables are pinned in memory ?
> >
> > -Nags
> >
> >
>sql

How to find which tables allocates disk space.

I just had truncate a table in a database that had grown too big, due to fawlty log settings. Now the database only hold 20 MB of data but the allocated size of the database is still 4 GB.
Is there any way I can find which one of the tables is responsible for allocating that amount of disk space? I haven't found any view that shows this. Nor did I find any interface to show any size properties regarding the system tables, like the "Task Pad" do for tables created by users.
please help me out here.
kind regards
sven
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...Sven
To reduce a physical size of the database perform DBCC SHRINKFILE command
Look at sp_spaceused stored procedure in the BOL.
"sven ekdahl" <sveek328@.student.liu.se> wrote in message
news:%23vS5wSZxEHA.3108@.TK2MSFTNGP14.phx.gbl...
> I just had truncate a table in a database that had grown too big, due to
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...|||Hi
Have a look at DBCC SHRINKDATABASE in BOL.
Regards
Mike
"sven ekdahl" wrote:
> I just had truncate a table in a database that had grown too big, due to fawlty log settings. Now the database only hold 20 MB of data but the allocated size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for allocating that amount of disk space? I haven't found any view that shows this. Nor did I find any interface to show any size properties regarding the system tables, like the "Task Pad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
>|||Mike
By using DBCC SHRINKDATABASE you cannot reduce a size of the database
smaller than this minimum size.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:A4C1B7E7-8C03-44C9-9BCD-406758D7937E@.microsoft.com...
> Hi
> Have a look at DBCC SHRINKDATABASE in BOL.
> Regards
> Mike
> "sven ekdahl" wrote:
> > I just had truncate a table in a database that had grown too big, due to
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.
> >
> > Is there any way I can find which one of the tables is responsible for
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.
> >
> > please help me out here.
> >
> > kind regards
> > sven
> >
> > **********************************************************************
> > Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> > Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
> >|||**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

How to find which tables allocates disk space.

I just had truncate a table in a database that had grown too big, due to fawlty log settings. Now the database only hold 20 MB of data but the allocated size of the database is still 4 GB.
Is there any way I can find which one of the tables is responsible for allocating that amount of disk space? I haven't found any view that shows this. Nor did I find any interface to show any size properties regarding the system tables, like the "Task Pad
" do for tables created by users.
please help me out here.
kind regards
sven
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Sven
To reduce a physical size of the database perform DBCC SHRINKFILE command
Look at sp_spaceused stored procedure in the BOL.
"sven ekdahl" <sveek328@.student.liu.se> wrote in message
news:%23vS5wSZxEHA.3108@.TK2MSFTNGP14.phx.gbl...
> I just had truncate a table in a database that had grown too big, due to
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
|||Hi
Have a look at DBCC SHRINKDATABASE in BOL.
Regards
Mike
"sven ekdahl" wrote:

> I just had truncate a table in a database that had grown too big, due to fawlty log settings. Now the database only hold 20 MB of data but the allocated size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for allocating that amount of disk space? I haven't found any view that shows this. Nor did I find any interface to show any size properties regarding the system tables, like the "Task P
ad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
>
|||Mike
By using DBCC SHRINKDATABASE you cannot reduce a size of the database
smaller than this minimum size.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:A4C1B7E7-8C03-44C9-9BCD-406758D7937E@.microsoft.com...[vbcol=seagreen]
> Hi
> Have a look at DBCC SHRINKDATABASE in BOL.
> Regards
> Mike
> "sven ekdahl" wrote:
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.[vbcol=seagreen]
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.[vbcol=seagreen]
ASP.NET resources...[vbcol=seagreen]
|||how do I call this SHRINKFILE procedure? What's meant with the logical file name of a database?
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||To find the logical file names you can use : sp_helpdb <dbanme>
"sven ekdahl" <sveek328@.student.liu.se> wrote in message
news:OIxjYiaxEHA.908@.TK2MSFTNGP11.phx.gbl...
> how do I call this SHRINKFILE procedure? What's meant with the logical
> file name of a database?
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
> ASP.NET resources...
|||************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

How to find which tables allocates disk space.

I just had truncate a table in a database that had grown too big, due to faw
lty log settings. Now the database only hold 20 MB of data but the allocated
size of the database is still 4 GB.
Is there any way I can find which one of the tables is responsible for alloc
ating that amount of disk space? I haven't found any view that shows this. N
or did I find any interface to show any size properties regarding the system
tables, like the "Task Pad
" do for tables created by users.
please help me out here.
kind regards
sven
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Sven
To reduce a physical size of the database perform DBCC SHRINKFILE command
Look at sp_spaceused stored procedure in the BOL.
"sven ekdahl" <sveek328@.student.liu.se> wrote in message
news:%23vS5wSZxEHA.3108@.TK2MSFTNGP14.phx.gbl...
> I just had truncate a table in a database that had grown too big, due to
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...|||Hi
Have a look at DBCC SHRINKDATABASE in BOL.
Regards
Mike
"sven ekdahl" wrote:

> I just had truncate a table in a database that had grown too big, due to f
awlty log settings. Now the database only hold 20 MB of data but the allocat
ed size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for allocating
that amount of disk space? I haven't found any view that shows this. Nor did I find
any interface to show any size properties regarding the system tables, like the "Tas
k P
ad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NE
T resources...
>|||Mike
By using DBCC SHRINKDATABASE you cannot reduce a size of the database
smaller than this minimum size.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:A4C1B7E7-8C03-44C9-9BCD-406758D7937E@.microsoft.com...[vbcol=seagreen]
> Hi
> Have a look at DBCC SHRINKDATABASE in BOL.
> Regards
> Mike
> "sven ekdahl" wrote:
>
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.[vbcol=seagreen]
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.[vbcol=seagreen]
ASP.NET resources...[vbcol=seagreen]|||how do I call this SHRINKFILE procedure? What's meant with the logical file
name of a database?
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||To find the logical file names you can use : sp_helpdb <dbanme>
"sven ekdahl" <sveek328@.student.liu.se> wrote in message
news:OIxjYiaxEHA.908@.TK2MSFTNGP11.phx.gbl...
> how do I call this SHRINKFILE procedure? What's meant with the logical
> file name of a database?
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
> ASP.NET resources...|||****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
KarenQuickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
--
David Portas
SQL Server MVP
--
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegroups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.giganews.com...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegroups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
Karen
Quickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
David Portas
SQL Server MVP
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegro ups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.giganews.com ...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael
|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.
|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegro ups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
KarenQuickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
David Portas
SQL Server MVP
--
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegroups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.gi
ganews.com...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegroups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
KarenQuickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
David Portas
SQL Server MVP
--
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegroups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.gi
ganews.com...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegroups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what tables/views/functions a stored proc uses

Hi All,
Is there any way How to find what tables/views/functions a stored proc
uses.
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***Try sp_depends
Thomas
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:%23VriiaVXFHA.1468@.tk2msftngp13.phx.gbl...
> Hi All,
> Is there any way How to find what tables/views/functions a stored proc
> uses.
> Thanks for your help.
> *** Sent via Developersdex http://www.examnotes.net ***|||Hai
Object that dependent on <<table_name>>
exec sp_MSdependencies N'[dbo].[<<Table_name>>]', null, 1315327
Object that <<table_name>> depends on
exec sp_MSdependencies N'[dbo].[<<Table_name>>]', null, 1053183
Thanks
NR. Harisutarsan
*** Sent via Developersdex http://www.examnotes.net ***|||Thanks. That works very well.
*** Sent via Developersdex http://www.examnotes.net ***sql

How to find what Tables and Indexes are on what filegroup

Hello,
Is there a query that can be written to determine what Tables and indexes
are on a particular file group?
Thanks
Anubis.sp_help 'YourTable' will return the filegroup in one of the
resultsets that is returned. There are also some
undocumented ways such as:
sp_objectfilegroup @.objid
or querying the system tables...something like:
SELECT so.name, sfg.groupname
FROM sysobjects so
INNER JOIN sysindexes si
ON so.id=si.id
INNER JOIN sysfilegroups sfg
ON si.groupid=sfg.groupid
WHERE si.indid < 2
AND so.type = 'U'
-Sue
On Tue, 14 Jun 2005 12:17:47 +1000, "Anubis"
<anubis@.iwwd.com> wrote:
>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>|||This is a multi-part message in MIME format.
--010807000500020704010007
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Depends on what info you want to find out. But it will be some
variation on this:
select object_name(i.[id]) as tablename, i.*
from dbo.sysindexes as i
inner join dbo.sysfilegroups as g on g.groupid = i.groupid
where g.groupname = 'PRIMARY'
If it's just names you're after then the column list in the select
statement will be something like "select object_name(i.[id]) as
tablename, i.[name] as indexname ..." but there's heaps of other info
you can find out from dbo.sysindexes, like the index type (clustered,
nonclustered, heap, LOB data - all from the indid), etc.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Anubis wrote:
>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>
>
--010807000500020704010007
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Depends on what info you want to find out. But it will be some
variation on this:<br>
</tt>
<blockquote><tt>select object_name(i.[id]) as tablename, i.*</tt><br>
<tt>from dbo.sysindexes as i</tt><br>
<tt> inner join dbo.sysfilegroups as g on g.groupid = i.groupid</tt><br>
<tt>where g.groupname = 'PRIMARY'<br>
</tt></blockquote>
<tt>If it's just names you're after then the column list in the select
statement will be something like "select object_name(i.[id]) as
tablename, i.[name] as indexname ..." but there's heaps of other info
you can find out from dbo.sysindexes, like the index type (clustered,
nonclustered, heap, LOB data - all from the indid), etc.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Anubis wrote:
<blockquote cite="miduyBFlhIcFHA.1148@.tk2msftngp13.phx.gbl" type="cite">
<pre wrap="">Hello,
Is there a query that can be written to determine what Tables and indexes
are on a particular file group?
Thanks
Anubis.
</pre>
</blockquote>
</body>
</html>
--010807000500020704010007--|||I wrote a script that does exactly what you ask (and more)
http://education.sqlfarms.com/ShowPost.aspx?PostID=48
for your convenient download. Enjoy.
The script lists all the tables and indexes filgroup, as well as provides
other information.
--
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

How to find what Tables and Indexes are on what filegroup

Hello,
Is there a query that can be written to determine what Tables and indexes
are on a particular file group?
Thanks
Anubis.
sp_help 'YourTable' will return the filegroup in one of the
resultsets that is returned. There are also some
undocumented ways such as:
sp_objectfilegroup @.objid
or querying the system tables...something like:
SELECT so.name, sfg.groupname
FROM sysobjects so
INNER JOIN sysindexes si
ON so.id=si.id
INNER JOIN sysfilegroups sfg
ON si.groupid=sfg.groupid
WHERE si.indid < 2
AND so.type = 'U'
-Sue
On Tue, 14 Jun 2005 12:17:47 +1000, "Anubis"
<anubis@.iwwd.com> wrote:

>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>
|||Depends on what info you want to find out. But it will be some
variation on this:
select object_name(i.[id]) as tablename, i.*
from dbo.sysindexes as i
inner join dbo.sysfilegroups as g on g.groupid = i.groupid
where g.groupname = 'PRIMARY'
If it's just names you're after then the column list in the select
statement will be something like "select object_name(i.[id]) as
tablename, i.[name] as indexname ..." but there's heaps of other info
you can find out from dbo.sysindexes, like the index type (clustered,
nonclustered, heap, LOB data - all from the indid), etc.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Anubis wrote:

>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>
>
|||I wrote a script that does exactly what you ask (and more)
http://education.sqlfarms.com/ShowPost.aspx?PostID=48
for your convenient download. Enjoy.
The script lists all the tables and indexes filgroup, as well as provides
other information.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

How to find what Tables and Indexes are on what filegroup

Hello,
Is there a query that can be written to determine what Tables and indexes
are on a particular file group?
Thanks
Anubis.sp_help 'YourTable' will return the filegroup in one of the
resultsets that is returned. There are also some
undocumented ways such as:
sp_objectfilegroup @.objid
or querying the system tables...something like:
SELECT so.name, sfg.groupname
FROM sysobjects so
INNER JOIN sysindexes si
ON so.id=si.id
INNER JOIN sysfilegroups sfg
ON si.groupid=sfg.groupid
WHERE si.indid < 2
AND so.type = 'U'
-Sue
On Tue, 14 Jun 2005 12:17:47 +1000, "Anubis"
<anubis@.iwwd.com> wrote:

>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>|||Depends on what info you want to find out. But it will be some
variation on this:
select object_name(i.[id]) as tablename, i.*
from dbo.sysindexes as i
inner join dbo.sysfilegroups as g on g.groupid = i.groupid
where g.groupname = 'PRIMARY'
If it's just names you're after then the column list in the select
statement will be something like "select object_name(i.[id]) as
tablename, i.[name] as indexname ..." but there's heaps of other info
you can find out from dbo.sysindexes, like the index type (clustered,
nonclustered, heap, LOB data - all from the indid), etc.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Anubis wrote:

>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>
>|||I wrote a script that does exactly what you ask (and more)
http://education.sqlfarms.com/ShowPost.aspx?PostID=48
for your convenient download. Enjoy.
The script lists all the tables and indexes filgroup, as well as provides
other information.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

How to find the second largest value in a field !

Hi,

i am taking the values from four tables ,

I am showing the Salesman in the descending order. according to their Sale Amount. by displaying in Descending, user can able to view the
salesman who sold for the highest amount.

Now I want to find the second highest Amount in the field.

for the highest and lowest we can use the Max and Min funtion.

for the second highest value, How can I write the query.

I already check the previous forums. But i couldn't get the idea.

Kindly reply me

Thank you very much,
Chock.Originally posted by chock
Hi,

i am taking the values from four tables ,

I am showing the Salesman in the descending order. according to their Sale Amount. by displaying in Descending, user can able to view the
salesman who sold for the highest amount.

Now I want to find the second highest Amount in the field.

for the highest and lowest we can use the Max and Min funtion.

for the second highest value, How can I write the query.

I already check the previous forums. But i couldn't get the idea.

Kindly reply me

Thank you very much,
Chock.
Well one way would be to say: what is the highest value after the highest value has been excluded (if you follow me):

SELECT MAX(amount)
FROM mytab
WHERE amount != (SELECT MAX(amount) FROM mytab);

Of course, you wouldn't want to use this recursive approach to get the 5th highest amount! For that, you could do:

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);

i.e. get the amount for which there are exactly 4 higher amounts in the table.|||Hi,

You send me two queries, the first query I understand it. But in the second query

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);
what's m1 and what's m2. In the previous query you didn't use the m1.

Actually Amount is the Field name we are going to compare and select.
and mytab is the Table Name.
I am new to this so I think i need some more o understand. can you please tell about the m1 and m2.

Thank you very much,
Chock.|||Originally posted by chock
Hi,

You send me two queries, the first query I understand it. But in the second query

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);
what's m1 and what's m2. In the previous query you didn't use the m1.

Actually Amount is the Field name we are going to compare and select.
and mytab is the Table Name.
I am new to this so I think i need some more o understand. can you please tell about the m1 and m2.

Thank you very much,
Chock.
m1 and m2 are "aliases". I made them up, because I wanted to use the same table "mytab" twice in the same query and compare values. Without aliases the query would be:

SELECT amount FROM mytab
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab
WHERE mytab.amount > mytab.amount
);

... which will return no data, because the condition "WHERE mytab.amount > mytab.amount" is nonsense. What I want to say is "WHERE mytab.amount (in this subquery) > mytab.amount (in the main query)". Aliases allow you to do that.|||tony, you may have confused the issue by jumping from the second highest to the fifth

here's another way to get the row with the second highest value:select Salesman, SaleAmount
from SalesTable
where SaleAmount =
( select max(SaleAmount)
from SalesTable
where SaleAmount <
( select max(SaleAmount)
from SalesTable
)
)in english, "get the row where the SaleAmount is the highest SaleAmount that is less than the highest overall SaleAmount"

wouldn't want to nest that too deeply, eh

i believe a good optimiser will evaluate the innermost first (it is not correlated), then the next inner, then do a straight retrieval -- i could be wrong, though (it has happened, and optimizer performance is not my long suit)

rudy
http://r937.com

How to find the NULL counts and non NULL counts?

Dear experts,
I am finding a LOT of rows with NULL columns in the Sybase
tables I'm querying.
Say, there is a table, with 100 rows.
25 rows are NULL
75 rows are NOT NULL.
What I'm trying to eliminate is:
select count(*)
from some_table
where fieldx is null
and then running the next query:
select count(*)
from some_table
where fieldx is NOT null
What functions can I use to run a query such as:
select count(f1( fieldx ),) AS count_of_null,
count(f2( fieldx ) ) AS count_of_not_null,
count(*)
from some_table
that would return one row that would look like:
count_of_null count_of_not_null count(*)
25 75 100
I know there is the ISNULL function. But that converts the NULL
to an actual number. Could I use other functions in conjunction with
it?
Thanks a lot!<dba_222@.yahoo.com> wrote in message
news:1162305541.838434.188080@.f16g2000cwb.googlegroups.com...
> Dear experts,
> I am finding a LOT of rows with NULL columns in the Sybase
> tables I'm querying.
> Say, there is a table, with 100 rows.
> 25 rows are NULL
> 75 rows are NOT NULL.
>
> What I'm trying to eliminate is:
> select count(*)
> from some_table
> where fieldx is null
> and then running the next query:
> select count(*)
> from some_table
> where fieldx is NOT null
>
> What functions can I use to run a query such as:
> select count(f1( fieldx ),) AS count_of_null,
> count(f2( fieldx ) ) AS count_of_not_null,
> count(*)
> from some_table
>
> that would return one row that would look like:
>
> count_of_null count_of_not_null count(*)
> 25 75 100
>
> I know there is the ISNULL function. But that converts the NULL
> to an actual number. Could I use other functions in conjunction with
> it?
SELECT COUNT(fieldx) AS count_of_not_null,
COUNT(*) - COUNT(fieldx) AS count_of_null
FROM some_table
Is how you would do it with MS SQL Server. Should also work with Sybase,
but I don't have a Sybase server to test on.|||SELECT COUNT(*) as TotalRows,
COUNT(Col1) as Col1_NotNull,
COUNT(Col2) as Col2_NotNull,
COUNT(Col3) as Col3_NotNull
FROM TableWithNulls
The first column tells you the total number of rows in the table, the
other columns the number of non-nulls for the column specified. Not
that you can deal with all the columns in one SELECT.
Roy Harvey
Beacon Falls, CT
On 31 Oct 2006 06:39:01 -0800, dba_222@.yahoo.com wrote:
>Dear experts,
>I am finding a LOT of rows with NULL columns in the Sybase
>tables I'm querying.
>Say, there is a table, with 100 rows.
>25 rows are NULL
>75 rows are NOT NULL.
>
>What I'm trying to eliminate is:
>select count(*)
>from some_table
>where fieldx is null
>and then running the next query:
>select count(*)
>from some_table
>where fieldx is NOT null
>
>What functions can I use to run a query such as:
>select count(f1( fieldx ),) AS count_of_null,
> count(f2( fieldx ) ) AS count_of_not_null,
> count(*)
>from some_table
>
>that would return one row that would look like:
>
>count_of_null count_of_not_null count(*)
>25 75 100
>
>I know there is the ISNULL function. But that converts the NULL
>to an actual number. Could I use other functions in conjunction with
>it?
>
>Thanks a lot!|||Brilliant!
I really should have thought of that.
But it was a looong tedious day yesterday.
Thanks a lot!
Mike C# wrote:
> <dba_222@.yahoo.com> wrote in message
> news:1162305541.838434.188080@.f16g2000cwb.googlegroups.com...
> > Dear experts,
> >
> > I am finding a LOT of rows with NULL columns in the Sybase
> > tables I'm querying.
> >
> > Say, there is a table, with 100 rows.
> > 25 rows are NULL
> > 75 rows are NOT NULL.
> >
> >
> > What I'm trying to eliminate is:
> >
> > select count(*)
> > from some_table
> > where fieldx is null
> >
> > and then running the next query:
> >
> > select count(*)
> > from some_table
> > where fieldx is NOT null
> >
> >
> > What functions can I use to run a query such as:
> >
> > select count(f1( fieldx ),) AS count_of_null,
> > count(f2( fieldx ) ) AS count_of_not_null,
> > count(*)
> > from some_table
> >
> >
> > that would return one row that would look like:
> >
> >
> > count_of_null count_of_not_null count(*)
> >
> > 25 75 100
> >
> >
> >
> > I know there is the ISNULL function. But that converts the NULL
> > to an actual number. Could I use other functions in conjunction with
> > it?
> SELECT COUNT(fieldx) AS count_of_not_null,
> COUNT(*) - COUNT(fieldx) AS count_of_null
> FROM some_table
> Is how you would do it with MS SQL Server. Should also work with Sybase,
> but I don't have a Sybase server to test on.

How to find the last updated tables in a database

Hi,

I am trying to create a data dictionary for a huge application which has aroung 300 tables in the database....when i perform any operation in the application some tables are updated.... can you help me to find out how can we find out the last updated tables in the database ?

Try using SQL Profiler to monitor which tables are being updated.

You can also search for SQL Dependency Analyzer - there are a few tools out there that will track this.

cheers,

Andrew

How to find strings in multiple tables

I have a database in SQL has 250 tables. I want to find out all the tables
which have certain string in it for example "computer1". Is there an easier
way to do this task rather than going into each table do a search?JL
This script has written by Narayana Vyas Kondreddi. Also I suggest to visit
his site (http://vyaskn.tripod.com )when you find a lot of examples and
useful scripts.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128), @.SearchStr2
nvarchar(110)
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName = (
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @.TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName = (
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@.TableName, 2)
AND TABLE_NAME = PARSENAME(@.TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @.TableName + '.' + @.ColumnName + ''', LEFT(' +
@.ColumnName + ', 3630)
FROM ' + @.TableName + ' (NOLOCK) ' +
' WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>|||Hi,
Have a look into the below code, I got this procedure from previous post.
This code will generate the Select statement for all the
Char, varchar, nvarchar,nchar columns for all the tables searching for the
string you are inputting.
All you have to do is just cut and paste the result of this procedure to a
query window and execute.
---
drop procedure ColSearch
go
create procedure ColSearch @.instr varchar(255), @.tablename varchar(255) =null
as
create table #colsearch (colid int identity (1,1),
colname varchar(255),
tablename varchar(255))
if @.tablename is not null
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and so.name = @.tablename
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
else
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
select 'select '+rtrim(colname)+' from '+rtrim(tablename)+' where
'+colname+' like ''%'+@.instr+'%'''
from #colsearch
order by colid
Thanks
Hari
MCDBA
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>