How to save entire database structure without data?

I want to save a complete copy of the database structure without any of the
data. Tables, Sprocs, Permissions, Triggers, etc.
How do I go about doing this and getting EVERYTHING except the data? Thanks
On Dec 11, 12:13 pm, "JTMZ" <J...@.hotmail.com> wrote:
> I want to save a complete copy of the database structure without any of the
> data. Tables, Sprocs, Permissions, Triggers, etc.
> How do I go about doing this and getting EVERYTHING except the data? Thanks
You can create script for the entire object in the database. Right
click on the database and select Tasks then Generate Scripts which
will open a window of SQL Server Scripts Wizard. The rest is self
explanotary.
Bulent

How to save entire database structure without data?

I want to save a complete copy of the database structure without any of the
data. Tables, Sprocs, Permissions, Triggers, etc.
How do I go about doing this and getting EVERYTHING except the data? ThanksOn Dec 11, 12:13 pm, "JTMZ" <J...@.hotmail.com> wrote:
> I want to save a complete copy of the database structure without any of the
> data. Tables, Sprocs, Permissions, Triggers, etc.
> How do I go about doing this and getting EVERYTHING except the data? Thanks
You can create script for the entire object in the database. Right
click on the database and select Tasks then Generate Scripts which
will open a window of SQL Server Scripts Wizard. The rest is self
explanotary.
Bulentsql

How to save entire database structure without data?

I want to save a complete copy of the database structure without any of the
data. Tables, Sprocs, Permissions, Triggers, etc.
How do I go about doing this and getting EVERYTHING except the data? ThanksOn Dec 11, 12:13 pm, "JTMZ" <J...@.hotmail.com> wrote:[vbcol=seagreen]
> I want to save a complete copy of the database structure without any of th
e
> data. Tables, Sprocs, Permissions, Triggers, etc.
> How do I go about doing this and getting EVERYTHING except the data? Thanks[/vbco
l]
You can create script for the entire object in the database. Right
click on the database and select Tasks then Generate Scripts which
will open a window of SQL Server Scripts Wizard. The rest is self
explanotary.
Bulent

how to save .jpg files in tables?

hi

i want to save .jpg files in my tables.
but i don't know how to do this.
there are some pics of a company's personels.

how to design a page for sending pics and save them in database?

thank uHi,

This has been explained clearly with a code sample at the following Url:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=81078

Regards,
Vikramsql

How to save "New" / "Updated" records only in to destination Tables

Hi

I have a requirement like, i need to save all the records from my Flat File on Monthly basis to my database table. In the next month, the flat file may be added with 10-20 records and also some updates happened to my old records. Though the faltfile is same for each month, the changes are occured in some records and also added with few records.

So when i am loading this data in to Database table, i need to just update the changed records and also needs to add the new records. I don't need to touch the remaining records.

How can i do that in SSIS 2005 using different data flow tasks ?

Thanks

Kumaran

Answers are right here: http://www.sqlis.com/default.aspx?311

-Jamie

|||

Hi Jamie

As per the URL you mentioned, i tried for the results.

I don't have any problem on identifying new records and inseting in to my database table using the "Conditional Split transformation".

But How to handle my few UPDATED records ? hoc can i identify that and update only those records in to my data base Table.

Thanks

Kumaran

|||

You could use another lookup on the records that find a match which basically does the same - but looks up against ALL columns in the target. If there are any rows that don't find a match then you know something has been changed.

-Jamie

How To Run SQL Script

Hi,

I have a SQL script which create few tables.
Currently, I have to paste the scripts to the SQL Enterprise Manager
and highlight one by one to run it. It can't seem to run it as "one
unit".

My script looks like below:

CREATE TABLE T1 AS ...;
CREATE TABLE T2 AS ...;

Any comments?

Thanks in advance.You can run them as a single unit, if these are just CREATE TABLE
statements. Are you getting any errors?

However if you are creating objects with cyclic dependencies you may have to
make sure the dependant objects must be created before the parent object.
One alternative to this is to use CREATE SCHEMA DDL & details can be found
in SQL Server Books Online.

--
- Anith
( Please reply to newsgroups only )

How to run replications with different order articles

Our customers requested to run different order of articles
when perfore replicaitons. EX we have a,b,c,...tables.
How can I set the SQL server to replicate table b and d
first, then do rest of tables?
Thanks in advance
The simplest way is to have 2 publications and synchronize one before the
other.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to run query on system tables of MS Jet

I am new to use Microsoft.Jet.OLEDB.4.0.

I need to run query (Select * from TABLE...) on system tables of my DB.
I can see data in those table from MS Access. But anytime I run query programatically, I got error saying

exception 80040E09, Record(s) cannot be read; no read permission on 'MSysRelationships'. (80040E09)

Can anyone tell me how to run query on sytem tables?

I can do this type of things in MS SQL Server.

Really appreciate your help!

/HannaI have exactly same problem. PLease help !

How to run a script -

Hello,
I am new to SQL2000, how do I take a script someone creates for creating
tables, and RUN it so it creates the phisical tables? Do I use the
Enterprise Manger and typing in a stored procedure?
Your help appreciated
taccea
Open it in Query Analyzer and hit the play (Execute Query) button...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"taccea" <taccea@.adelphia.net> wrote in message
news:PaydnTRP6oJMkOXcRVn-1Q@.adelphia.com...
> Hello,
> I am new to SQL2000, how do I take a script someone creates for creating
> tables, and RUN it so it creates the phisical tables? Do I use the
> Enterprise Manger and typing in a stored procedure?
> Your help appreciated
> taccea
>
|||Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||thanks
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uitbqS6tEHA.1356@.TK2MSFTNGP11.phx.gbl...
> Open it in Query Analyzer and hit the play (Execute Query) button...
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "taccea" <taccea@.adelphia.net> wrote in message
> news:PaydnTRP6oJMkOXcRVn-1Q@.adelphia.com...
>
|||Thanks to ALL
Taccea
"taccea" <taccea@.adelphia.net> wrote in message
news:PaydnTRP6oJMkOXcRVn-1Q@.adelphia.com...
> Hello,
> I am new to SQL2000, how do I take a script someone creates for creating
> tables, and RUN it so it creates the phisical tables? Do I use the
> Enterprise Manger and typing in a stored procedure?
> Your help appreciated
> taccea
>

How to run *.sql ddl automatically

I have tables, views, functions, procedures created in a database with test
data
I would like to be able to automatically kick off the Create sql files via a
script/bat file and bypass having to open all the *sql files in Query
Analyzer.
Can anyone point me to knowledge based articles or How-Tos on this topic?
thx
use copy command to append all files into one, and run osql with -i to input
the file, lastly, schedule osql in a scheduled batch process, or SQL job.
"TroyS" <troy.stauber@.ilg.com> wrote in message
news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>I have tables, views, functions, procedures created in a database with test
>data
> I would like to be able to automatically kick off the Create sql files via
> a script/bat file and bypass having to open all the *sql files in Query
> Analyzer.
> Can anyone point me to knowledge based articles or How-Tos on this topic?
> thx
>
|||For 2000, use OSQL.EXE. For 2005, use SQLCMD.EXE. To iterate over several files, use FOR in your
batch file:
for %%i in (*.sql) do osql -E -d pubs -STIBWORK\RTM -i %%i
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TroyS" <troy.stauber@.ilg.com> wrote in message news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>I have tables, views, functions, procedures created in a database with test data
> I would like to be able to automatically kick off the Create sql files via a script/bat file and
> bypass having to open all the *sql files in Query Analyzer.
> Can anyone point me to knowledge based articles or How-Tos on this topic?
> thx
>
|||thx. i will try the FOR as i have multiple files
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237qXfcoAGHA.1460@.TK2MSFTNGP14.phx.gbl...
> For 2000, use OSQL.EXE. For 2005, use SQLCMD.EXE. To iterate over several
> files, use FOR in your batch file:
> for %%i in (*.sql) do osql -E -d pubs -STIBWORK\RTM -i %%i
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TroyS" <troy.stauber@.ilg.com> wrote in message
> news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>
|||thx. i'm having a blond moment and had in my mind multiple files to deal
with rather than just paste everything into 1 file.
"Richard Ding" <richard.ding@.monster.com> wrote in message
news:u8RjaYoAGHA.2036@.TK2MSFTNGP14.phx.gbl...
> use copy command to append all files into one, and run osql with -i to
> input the file, lastly, schedule osql in a scheduled batch process, or SQL
> job.
>
> "TroyS" <troy.stauber@.ilg.com> wrote in message
> news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>

How to run *.sql ddl automatically

I have tables, views, functions, procedures created in a database with test
data
I would like to be able to automatically kick off the Create sql files via a
script/bat file and bypass having to open all the *sql files in Query
Analyzer.
Can anyone point me to knowledge based articles or How-Tos on this topic?
thxuse copy command to append all files into one, and run osql with -i to input
the file, lastly, schedule osql in a scheduled batch process, or SQL job.
"TroyS" <troy.stauber@.ilg.com> wrote in message
news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>I have tables, views, functions, procedures created in a database with test
>data
> I would like to be able to automatically kick off the Create sql files via
> a script/bat file and bypass having to open all the *sql files in Query
> Analyzer.
> Can anyone point me to knowledge based articles or How-Tos on this topic?
> thx
>|||For 2000, use OSQL.EXE. For 2005, use SQLCMD.EXE. To iterate over several files, use FOR in your
batch file:
for %%i in (*.sql) do osql -E -d pubs -STIBWORK\RTM -i %%i
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TroyS" <troy.stauber@.ilg.com> wrote in message news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>I have tables, views, functions, procedures created in a database with test data
> I would like to be able to automatically kick off the Create sql files via a script/bat file and
> bypass having to open all the *sql files in Query Analyzer.
> Can anyone point me to knowledge based articles or How-Tos on this topic?
> thx
>|||thx. i will try the FOR as i have multiple files
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237qXfcoAGHA.1460@.TK2MSFTNGP14.phx.gbl...
> For 2000, use OSQL.EXE. For 2005, use SQLCMD.EXE. To iterate over several
> files, use FOR in your batch file:
> for %%i in (*.sql) do osql -E -d pubs -STIBWORK\RTM -i %%i
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TroyS" <troy.stauber@.ilg.com> wrote in message
> news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>>I have tables, views, functions, procedures created in a database with
>>test data
>> I would like to be able to automatically kick off the Create sql files
>> via a script/bat file and bypass having to open all the *sql files in
>> Query Analyzer.
>> Can anyone point me to knowledge based articles or How-Tos on this topic?
>> thx
>|||thx. i'm having a blond moment and had in my mind multiple files to deal
with rather than just paste everything into 1 file.
"Richard Ding" <richard.ding@.monster.com> wrote in message
news:u8RjaYoAGHA.2036@.TK2MSFTNGP14.phx.gbl...
> use copy command to append all files into one, and run osql with -i to
> input the file, lastly, schedule osql in a scheduled batch process, or SQL
> job.
>
> "TroyS" <troy.stauber@.ilg.com> wrote in message
> news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>>I have tables, views, functions, procedures created in a database with
>>test data
>> I would like to be able to automatically kick off the Create sql files
>> via a script/bat file and bypass having to open all the *sql files in
>> Query Analyzer.
>> Can anyone point me to knowledge based articles or How-Tos on this topic?
>> thx
>

How to run *.sql ddl automatically

I have tables, views, functions, procedures created in a database with test
data
I would like to be able to automatically kick off the Create sql files via a
script/bat file and bypass having to open all the *sql files in Query
Analyzer.
Can anyone point me to knowledge based articles or How-Tos on this topic?
thxuse copy command to append all files into one, and run osql with -i to input
the file, lastly, schedule osql in a scheduled batch process, or SQL job.
"TroyS" <troy.stauber@.ilg.com> wrote in message
news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>I have tables, views, functions, procedures created in a database with test
>data
> I would like to be able to automatically kick off the Create sql files via
> a script/bat file and bypass having to open all the *sql files in Query
> Analyzer.
> Can anyone point me to knowledge based articles or How-Tos on this topic?
> thx
>|||For 2000, use OSQL.EXE. For 2005, use SQLCMD.EXE. To iterate over several fi
les, use FOR in your
batch file:
for %%i in (*.sql) do osql -E -d pubs -STIBWORK\RTM -i %%i
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TroyS" <troy.stauber@.ilg.com> wrote in message news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl..
.
>I have tables, views, functions, procedures created in a database with test
data
> I would like to be able to automatically kick off the Create sql files via
a script/bat file and
> bypass having to open all the *sql files in Query Analyzer.
> Can anyone point me to knowledge based articles or How-Tos on this topic?
> thx
>|||thx. i will try the FOR as i have multiple files
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237qXfcoAGHA.1460@.TK2MSFTNGP14.phx.gbl...
> For 2000, use OSQL.EXE. For 2005, use SQLCMD.EXE. To iterate over several
> files, use FOR in your batch file:
> for %%i in (*.sql) do osql -E -d pubs -STIBWORK\RTM -i %%i
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TroyS" <troy.stauber@.ilg.com> wrote in message
> news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>|||thx. i'm having a blond moment and had in my mind multiple files to deal
with rather than just paste everything into 1 file.
"Richard Ding" <richard.ding@.monster.com> wrote in message
news:u8RjaYoAGHA.2036@.TK2MSFTNGP14.phx.gbl...
> use copy command to append all files into one, and run osql with -i to
> input the file, lastly, schedule osql in a scheduled batch process, or SQL
> job.
>
> "TroyS" <troy.stauber@.ilg.com> wrote in message
> news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>

How to Rollback Transactions

hi everybody,

i have 4 flat files from a source folder which updates four different tables, this has to be done parallely,on success of this transaction the files have to be moved to another folder.

my problem comes here,now if there is any problem in moving any file to another folder,that particular transaction has to be rolledback without affecting others.i tried setting the transaction property of the control flow,but it rollbacks all the transaction..

please help me on this

You can scope transactions to a container, not just a package. Use Sequence containers to give some separation between the four load processes and their file operations.

You could also use the manual method of managing transactions, (http://blogs.conchango.com/jamiethomson/archive/2005/08/20/SSIS-Nugget_3A00_-RetainSameConnection-property-of-the-OLE-DB-Connection-Manager.aspx), just repeat the pattern four times, once for each load, with four separate connections, one for each load, and the associate SQL tasks used to manage thetransaction.

|||

hi darren,

thanks for your post it was very helpful.

i have used foreach containers for the four load process as i can get multiple files.

now i have one more problem,my input file names will have format like this

yyyymmdd_salesdataforproduct_yyyymmdd_hhmmss.txt

here the first date is bussiness date and the second one is the sysytem date..if i get multiple file i have to proceess considering the second date.but by default the foreach loop is considering the first date,what can i do to ensure that only second is used to process my files.

thanks in advance

srikanth

|||

You could make the foreach loop to go through all files and then use expressions in the precedence constraints to decide whether a file needs to be processed or not. You may need aditional variables to get the dates and compare it. A simplier example here:

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

How to Rollback Transactions

hi everybody,

i have 4 flat files from a source folder which updates four different tables, this has to be done parallely,on success of this transaction the files have to be moved to another folder.

my problem comes here,now if there is any problem in moving any file to another folder,that particular transaction has to be rolledback without affecting others.i tried setting the transaction property of the control flow,but it rollbacks all the transaction..

please help me on this

You can scope transactions to a container, not just a package. Use Sequence containers to give some separation between the four load processes and their file operations.

You could also use the manual method of managing transactions, (http://blogs.conchango.com/jamiethomson/archive/2005/08/20/SSIS-Nugget_3A00_-RetainSameConnection-property-of-the-OLE-DB-Connection-Manager.aspx), just repeat the pattern four times, once for each load, with four separate connections, one for each load, and the associate SQL tasks used to manage thetransaction.

|||

hi darren,

thanks for your post it was very helpful.

i have used foreach containers for the four load process as i can get multiple files.

now i have one more problem,my input file names will have format like this

yyyymmdd_salesdataforproduct_yyyymmdd_hhmmss.txt

here the first date is bussiness date and the second one is the sysytem date..if i get multiple file i have to proceess considering the second date.but by default the foreach loop is considering the first date,what can i do to ensure that only second is used to process my files.

thanks in advance

srikanth

|||

You could make the foreach loop to go through all files and then use expressions in the precedence constraints to decide whether a file needs to be processed or not. You may need aditional variables to get the dates and compare it. A simplier example here:

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

How to return all tables that have an index?

Using 2005
I want to generate a list of all tables that have indexes, so I can reindex
them. I don't want to bother doing a reindex on a table that has no
indexes. I thought it would be easy enough to pull this data from
sys.indexes (or something like that), but it is quirky. The following query
will return some tables, showing them as having 1 index, when really they
have NO indexes. But yet other tables result with having 1 index, when that
is correct. And this query also returns dtproperties, which is not one of
my user tables, so that also makes this query incorrect.
This is what I'm using, and maybe someone can help fine tune it so it is
100% accurate... Try this against any of your DBs and you'll see it is not
quite accurate even though mostly accurate...
Select object_name(object_id) as TableName, count(object_id) as NumIndexes,
object_id
from sys.indexes
where object_id>1000 -- eliminate some system table junk below 1000
group by object_id
order by TableName
If you just want a list of tables that contain indexes, you can do this:
SELECT name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
ORDER BY name;
If you want the tables and the index names, try this variation:
SELECT
t.name, i.name, i.type_desc
FROM sys.indexes AS i INNER JOIN
sys.tables AS t ON t.object_id = i.object_id
WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
ORDER BY t.name
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
> Using 2005
> I want to generate a list of all tables that have indexes, so I can
> reindex
> them. I don't want to bother doing a reindex on a table that has no
> indexes. I thought it would be easy enough to pull this data from
> sys.indexes (or something like that), but it is quirky. The following
> query
> will return some tables, showing them as having 1 index, when really they
> have NO indexes. But yet other tables result with having 1 index, when
> that
> is correct. And this query also returns dtproperties, which is not one
> of
> my user tables, so that also makes this query incorrect.
> This is what I'm using, and maybe someone can help fine tune it so it is
> 100% accurate... Try this against any of your DBs and you'll see it is
> not
> quite accurate even though mostly accurate...
> Select object_name(object_id) as TableName, count(object_id) as
> NumIndexes,
> object_id
> from sys.indexes
> where object_id>1000 -- eliminate some system table junk below 1000
> group by object_id
> order by TableName
>
|||Thank you. Why does dtproperties show up as a user table? There was a
known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
user table, but I figured something in SQL 2005 would prevent it from
appearing as a user table.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> If you just want a list of tables that contain indexes, you can do this:
> SELECT name
> FROM sys.tables
> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
> ORDER BY name;
> If you want the tables and the index names, try this variation:
> SELECT
> t.name, i.name, i.type_desc
> FROM sys.indexes AS i INNER JOIN
> sys.tables AS t ON t.object_id = i.object_id
> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
> ORDER BY t.name
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
they
>
|||> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
I'm not sure. I'll see what I can find out.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> they
>
|||Go add to Gail's response, you can exclude dtproperties with:
OBJECTPROPERTY(t.object_id, 'IsMsShipped') = 1
Note that this won't work for some other tables, like sysdiagrams. I think
the underlying issue is that there isn't a consistent definition of what a
'user object' is. In other words, should objects created after installation
by Microsoft or third party tools be considered system objects? Perhaps
there should be a 'NonUserObject' OBJECTPROPERTY so that these objects can
be excluded from script.
In any case, I wouldn't expect any harm in including tool-created objects as
part of your normal maintenance.
Hope this helps.
Dan Guzman
SQL Server MVP
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> they
>

How to return all tables that have an index?

Using 2005
I want to generate a list of all tables that have indexes, so I can reindex
them. I don't want to bother doing a reindex on a table that has no
indexes. I thought it would be easy enough to pull this data from
sys.indexes (or something like that), but it is quirky. The following query
will return some tables, showing them as having 1 index, when really they
have NO indexes. But yet other tables result with having 1 index, when that
is correct. And this query also returns dtproperties, which is not one of
my user tables, so that also makes this query incorrect.
This is what I'm using, and maybe someone can help fine tune it so it is
100% accurate... Try this against any of your DBs and you'll see it is not
quite accurate even though mostly accurate...
Select object_name(object_id) as TableName, count(object_id) as NumIndexes,
object_id
from sys.indexes
where object_id>1000 -- eliminate some system table junk below 1000
group by object_id
order by TableNameIf you just want a list of tables that contain indexes, you can do this:
SELECT name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
ORDER BY name;
If you want the tables and the index names, try this variation:
SELECT
t.name, i.name, i.type_desc
FROM sys.indexes AS i INNER JOIN
sys.tables AS t ON t.object_id = i.object_id
WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
ORDER BY t.name
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
> Using 2005
> I want to generate a list of all tables that have indexes, so I can
> reindex
> them. I don't want to bother doing a reindex on a table that has no
> indexes. I thought it would be easy enough to pull this data from
> sys.indexes (or something like that), but it is quirky. The following
> query
> will return some tables, showing them as having 1 index, when really they
> have NO indexes. But yet other tables result with having 1 index, when
> that
> is correct. And this query also returns dtproperties, which is not one
> of
> my user tables, so that also makes this query incorrect.
> This is what I'm using, and maybe someone can help fine tune it so it is
> 100% accurate... Try this against any of your DBs and you'll see it is
> not
> quite accurate even though mostly accurate...
> Select object_name(object_id) as TableName, count(object_id) as
> NumIndexes,
> object_id
> from sys.indexes
> where object_id>1000 -- eliminate some system table junk below 1000
> group by object_id
> order by TableName
>|||Thank you. Why does dtproperties show up as a user table? There was a
known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
user table, but I figured something in SQL 2005 would prevent it from
appearing as a user table.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> If you just want a list of tables that contain indexes, you can do this:
> SELECT name
> FROM sys.tables
> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
> ORDER BY name;
> If you want the tables and the index names, try this variation:
> SELECT
> t.name, i.name, i.type_desc
> FROM sys.indexes AS i INNER JOIN
> sys.tables AS t ON t.object_id = i.object_id
> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
> ORDER BY t.name
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
they[vbcol=seagreen]
>|||> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
I'm not sure. I'll see what I can find out.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> they
>|||Go add to Gail's response, you can exclude dtproperties with:
OBJECTPROPERTY(t.object_id, 'IsMsShipped') = 1
Note that this won't work for some other tables, like sysdiagrams. I think
the underlying issue is that there isn't a consistent definition of what a
'user object' is. In other words, should objects created after installation
by Microsoft or third party tools be considered system objects? Perhaps
there should be a 'NonUserObject' OBJECTPROPERTY so that these objects can
be excluded from script.
In any case, I wouldn't expect any harm in including tool-created objects as
part of your normal maintenance.
Hope this helps.
Dan Guzman
SQL Server MVP
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> they
>

How to return all tables that have an index?

Using 2005
I want to generate a list of all tables that have indexes, so I can reindex
them. I don't want to bother doing a reindex on a table that has no
indexes. I thought it would be easy enough to pull this data from
sys.indexes (or something like that), but it is quirky. The following query
will return some tables, showing them as having 1 index, when really they
have NO indexes. But yet other tables result with having 1 index, when that
is correct. And this query also returns dtproperties, which is not one of
my user tables, so that also makes this query incorrect.
This is what I'm using, and maybe someone can help fine tune it so it is
100% accurate... Try this against any of your DBs and you'll see it is not
quite accurate even though mostly accurate...
Select object_name(object_id) as TableName, count(object_id) as NumIndexes,
object_id
from sys.indexes
where object_id>1000 -- eliminate some system table junk below 1000
group by object_id
order by TableNameIf you just want a list of tables that contain indexes, you can do this:
SELECT name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
ORDER BY name;
If you want the tables and the index names, try this variation:
SELECT
t.name, i.name, i.type_desc
FROM sys.indexes AS i INNER JOIN
sys.tables AS t ON t.object_id = i.object_id
WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
ORDER BY t.name
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
> Using 2005
> I want to generate a list of all tables that have indexes, so I can
> reindex
> them. I don't want to bother doing a reindex on a table that has no
> indexes. I thought it would be easy enough to pull this data from
> sys.indexes (or something like that), but it is quirky. The following
> query
> will return some tables, showing them as having 1 index, when really they
> have NO indexes. But yet other tables result with having 1 index, when
> that
> is correct. And this query also returns dtproperties, which is not one
> of
> my user tables, so that also makes this query incorrect.
> This is what I'm using, and maybe someone can help fine tune it so it is
> 100% accurate... Try this against any of your DBs and you'll see it is
> not
> quite accurate even though mostly accurate...
> Select object_name(object_id) as TableName, count(object_id) as
> NumIndexes,
> object_id
> from sys.indexes
> where object_id>1000 -- eliminate some system table junk below 1000
> group by object_id
> order by TableName
>|||Thank you. Why does dtproperties show up as a user table? There was a
known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
user table, but I figured something in SQL 2005 would prevent it from
appearing as a user table.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> If you just want a list of tables that contain indexes, you can do this:
> SELECT name
> FROM sys.tables
> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
> ORDER BY name;
> If you want the tables and the index names, try this variation:
> SELECT
> t.name, i.name, i.type_desc
> FROM sys.indexes AS i INNER JOIN
> sys.tables AS t ON t.object_id = i.object_id
> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
> ORDER BY t.name
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
> > Using 2005
> >
> > I want to generate a list of all tables that have indexes, so I can
> > reindex
> > them. I don't want to bother doing a reindex on a table that has no
> > indexes. I thought it would be easy enough to pull this data from
> > sys.indexes (or something like that), but it is quirky. The following
> > query
> > will return some tables, showing them as having 1 index, when really
they
> > have NO indexes. But yet other tables result with having 1 index, when
> > that
> > is correct. And this query also returns dtproperties, which is not one
> > of
> > my user tables, so that also makes this query incorrect.
> >
> > This is what I'm using, and maybe someone can help fine tune it so it is
> > 100% accurate... Try this against any of your DBs and you'll see it is
> > not
> > quite accurate even though mostly accurate...
> >
> > Select object_name(object_id) as TableName, count(object_id) as
> > NumIndexes,
> > object_id
> > from sys.indexes
> > where object_id>1000 -- eliminate some system table junk below 1000
> > group by object_id
> > order by TableName
> >
> >
>|||> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
I'm not sure. I'll see what I can find out.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
>> If you just want a list of tables that contain indexes, you can do this:
>> SELECT name
>> FROM sys.tables
>> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
>> ORDER BY name;
>> If you want the tables and the index names, try this variation:
>> SELECT
>> t.name, i.name, i.type_desc
>> FROM sys.indexes AS i INNER JOIN
>> sys.tables AS t ON t.object_id = i.object_id
>> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
>> ORDER BY t.name
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "HK" <replywithingroup@.notreal.com> wrote in message
>> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
>> > Using 2005
>> >
>> > I want to generate a list of all tables that have indexes, so I can
>> > reindex
>> > them. I don't want to bother doing a reindex on a table that has no
>> > indexes. I thought it would be easy enough to pull this data from
>> > sys.indexes (or something like that), but it is quirky. The following
>> > query
>> > will return some tables, showing them as having 1 index, when really
> they
>> > have NO indexes. But yet other tables result with having 1 index, when
>> > that
>> > is correct. And this query also returns dtproperties, which is not
>> > one
>> > of
>> > my user tables, so that also makes this query incorrect.
>> >
>> > This is what I'm using, and maybe someone can help fine tune it so it
>> > is
>> > 100% accurate... Try this against any of your DBs and you'll see it is
>> > not
>> > quite accurate even though mostly accurate...
>> >
>> > Select object_name(object_id) as TableName, count(object_id) as
>> > NumIndexes,
>> > object_id
>> > from sys.indexes
>> > where object_id>1000 -- eliminate some system table junk below 1000
>> > group by object_id
>> > order by TableName
>> >
>> >
>>
>|||Go add to Gail's response, you can exclude dtproperties with:
OBJECTPROPERTY(t.object_id, 'IsMsShipped') = 1
Note that this won't work for some other tables, like sysdiagrams. I think
the underlying issue is that there isn't a consistent definition of what a
'user object' is. In other words, should objects created after installation
by Microsoft or third party tools be considered system objects? Perhaps
there should be a 'NonUserObject' OBJECTPROPERTY so that these objects can
be excluded from script.
In any case, I wouldn't expect any harm in including tool-created objects as
part of your normal maintenance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"HK" <replywithingroup@.notreal.com> wrote in message
news:Kg%Uf.11875$w86.7016@.tornado.socal.rr.com...
> Thank you. Why does dtproperties show up as a user table? There was a
> known bug where Visual Studio, accessing sql 7 or 2000, reports this as a
> user table, but I figured something in SQL 2005 would prevent it from
> appearing as a user table.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e7GP$m5TGHA.5908@.TK2MSFTNGP14.phx.gbl...
>> If you just want a list of tables that contain indexes, you can do this:
>> SELECT name
>> FROM sys.tables
>> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1
>> ORDER BY name;
>> If you want the tables and the index names, try this variation:
>> SELECT
>> t.name, i.name, i.type_desc
>> FROM sys.indexes AS i INNER JOIN
>> sys.tables AS t ON t.object_id = i.object_id
>> WHERE (OBJECTPROPERTY(t.object_id, 'IsIndexed') = 1)
>> ORDER BY t.name
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "HK" <replywithingroup@.notreal.com> wrote in message
>> news:Kl_Uf.9490$WK1.2824@.tornado.socal.rr.com...
>> > Using 2005
>> >
>> > I want to generate a list of all tables that have indexes, so I can
>> > reindex
>> > them. I don't want to bother doing a reindex on a table that has no
>> > indexes. I thought it would be easy enough to pull this data from
>> > sys.indexes (or something like that), but it is quirky. The following
>> > query
>> > will return some tables, showing them as having 1 index, when really
> they
>> > have NO indexes. But yet other tables result with having 1 index, when
>> > that
>> > is correct. And this query also returns dtproperties, which is not
>> > one
>> > of
>> > my user tables, so that also makes this query incorrect.
>> >
>> > This is what I'm using, and maybe someone can help fine tune it so it
>> > is
>> > 100% accurate... Try this against any of your DBs and you'll see it is
>> > not
>> > quite accurate even though mostly accurate...
>> >
>> > Select object_name(object_id) as TableName, count(object_id) as
>> > NumIndexes,
>> > object_id
>> > from sys.indexes
>> > where object_id>1000 -- eliminate some system table junk below 1000
>> > group by object_id
>> > order by TableName
>> >
>> >
>>
>

how to return all store procedure names from a databases?

Hi all,

I require a script to get all store procedure names from a database. I managed to find a script on how to return all the tables names from a db . I was thinking there could be script that could do the same thing but instead it returns the sp names

Thanks

Matthew

One way would be to search the sysobjects table:

select name
from sysobjects
where type = 'P'
order by name

Another (better) alternative is:

select routine_name
from information_schema.routines
where routine_type = 'PROCEDURE'
order by routine_name

|||

With SQL 2005, use:

SELECT Name
FROM sys.procedures

|||

The examples below will return all non-system stored procedures.

Chris

--SQL Server 2000
SELECT [name]
FROM dbo.sysobjects
WHERE OBJECTPROPERTY([id], 'IsProcedure') = 1
AND OBJECTPROPERTY([id], 'IsMSShipped') = 0

--SQL Server 2005
SELECT [name]
FROM sys.procedures
WHERE OBJECTPROPERTY([object_id], 'IsMSShipped') = 0

how to retrieve values from four tables at a time

i have to retrieve all columns of four table except common field(which is primary key in 1 table and foreign key in other table) will come only once from main table where condition is given by user. eg

table1- id(pk), name sex
table2- id(fk), address,contactno
table3- salary, pf,other,id(fk)
table4-id(fk), language,department

now i have to pick --id,name, sex,address,contactno,salary,pf, other,language,department-- where id= given by user.
i am using sql server,asp.net,c#
please reply fast
regards
sudha

Quote:

Originally Posted by sudhashekhar30

i have to retrieve all columns of four table except common field(which is primary key in 1 table and foreign key in other table) will come only once from main table where condition is given by user. eg

table1- id(pk), name sex
table2- id(fk), address,contactno
table3- salary, pf,other,id(fk)
table4-id(fk), language,department

now i have to pick --id,name, sex,address,contactno,salary,pf, other,language,department-- where id= given by user.
i am using sql server,asp.net,c#
please reply fast
regards
sudha

hi
try this>>>

select table1.id,table1.name, table1.sex,table2.address,table2.contactno,table3. salary,table3.pf, table3.other,table4.language,table4.departments from table1,table2,table3,table4

where table1.id=table2.id=table3.id=table4.id;

by
sankar|||

Quote:

Originally Posted by sanbala

hi
try this>>>

select table1.id,table1.name, table1.sex,table2.address,table2.contactno,table3. salary,table3.pf, table3.other,table4.language,table4.departments from table1,table2,table3,table4

where table1.id=table2.id=table3.id=table4.id;

by
sankar

thanks 4r ur reply mr shankar.
i did it. its something like dis--
"select e.*,paddress,pstate,ptaddress,designation,basicsal ary from empinfo e join address on e.empcode=address.empcode and e.empcode= @.id join salary s on e.empcode=s.empcode and e.empcode=@.id"
here is only 3 table.|||

Quote:

Originally Posted by sudhashekhar30

thanks 4r ur reply mr shankar.
i did it. its something like dis--
"select e.*,paddress,pstate,ptaddress,designation,basicsal ary from empinfo e join address on e.empcode=address.empcode and e.empcode= @.id join salary s on e.empcode=s.empcode and e.empcode=@.id"
here is only 3 table.

Hi sudha
i coundn't get you.what you trying to asking.
specify clearly...

by
sankar.b
bsankarit@.gmail.com

How to retrieve the (MB) size of all tables in a database

I have a database under SQL2005 that in a 24 hour period has increased from
200MB to 2GB (this is the dbf file size not the log file size) and there are
no obvious reasons for the increase. We have also tried Shrinking both the D
B
and file with little reduction in size. Is there a way to query the database
and find out the sizes of tables etc to help with further investigation?
Looking at the properties of each in Management studio really isn't feasible
due to the number of objects. TIA
AntonyThe sp below will return all the tables in a database, by reserved size
desc (large to small)
Run this sp in the database of concern:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[BigTables]
AS
/ ****************************************
***********************************
***********
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@.sqlteam.com
* v1.1
*
****************************************
************************************
**********/
declare @.id int
declare @.type character(2)
declare @.pages int
declare @.dbname sysname
declare @.dbsize dec(15,0)
declare @.bytesperpage dec(15,0)
declare @.pagesperMB dec(15,0)
create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = '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_MB = ltrim(str(reserved * d.low / 1048576.,15,0) + ' ' +
'MB'),
data_MB = ltrim(str(data * d.low / 1048576.,15,0) + ' ' + 'MB'),
index_size_MB = ltrim(str(indexp * d.low / 1048576.,15,0) + ' ' +
'MB'),
unused_MB = ltrim(str(unused * d.low / 1048576.,15,0) + ' ' + 'MB')
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|||Antony wrote:
> I have a database under SQL2005 that in a 24 hour period has increased fro
m
> 200MB to 2GB (this is the dbf file size not the log file size) and there a
re
> no obvious reasons for the increase. We have also tried Shrinking both the
DB
> and file with little reduction in size. Is there a way to query the databa
se
> and find out the sizes of tables etc to help with further investigation?
> Looking at the properties of each in Management studio really isn't feasib
le
> due to the number of objects. TIA
> Antony
There is a known bug with the auto-growth function in SQL 2005. Sounds
like that might be your problem.
http://connect.microsoft.com/SQLSer...=12717
7
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This might be useful.
CREATE TABLE #Tables
( [name] nvarchar(20),
[rows] char(11),
[reserved] varchar(18),
[data] varchar(18),
[index_size] varchar(18),
[unused] varchar(18)
)
EXECUTE sp_MSForEachTable 'INSERT INTO #Tables EXECUTE sp_spaceused [?]'
SELECT
[name],
[data]
FROM #Tables
DROP TABLE #Tables
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Antony" <info AT webpc DOT biz> wrote in message news:u0L0oEKFHHA.3212@.TK2MSFTNGP02.phx.gbl
..
>I have a database under SQL2005 that in a 24 hour period has increased from
> 200MB to 2GB (this is the dbf file size not the log file size) and there a
re
> no obvious reasons for the increase. We have also tried Shrinking both the
DB
> and file with little reduction in size. Is there a way to query the databa
se
> and find out the sizes of tables etc to help with further investigation?
> Looking at the properties of each in Management studio really isn't feasib
le
> due to the number of objects. TIA
> Antony|||Thanks everyone for the advice. I found the table that was causing the issue
.
Antony
On 11/30/2006 12:04:10 PM, "Antony" wrote:
>I have a database under SQL2005 that in a 24 hour period has increased from
>200MB to 2GB (this is the dbf file size not the log file size) and there ar
e
>no obvious reasons for the increase. We have also tried Shrinking both the
DB
>and file with little reduction in size. Is there a way to query the databas
e
>and find out the sizes of tables etc to help with further investigation?
>Looking at the properties of each in Management studio really isn't feasibl
e
>due to the number of objects. TIA
>Antony