Load Ordering for Dimension and Fact tables

Hi ,

I have situation where I get data from SRC Flat file and have to load Dimensional table and also fact table, using same data flow(have no other choice since I have to unpivot some src data). Since I have to load both tables in same data flow, I have to have a way to put load ordering constraint (I know informatica allows that). Does any one have any idea on how this can be done in SSIS?

I would be really grateful.

Thanks

The SSIS package designer contains a Control Flow tab and a Data Flow Tab. On the Control Flow tab, you would create two Data Flow tasks linked by a precedence constraint. The first task would load the dimension data and the second would load the fact data only if the first task succeeds or completes depending on the precedence conditions you configure.

Was your question this elementary?

|||In my question I said, I can't use two data flows, I have to use only one data flow. So is there a way to this?

Thanks,|||

DW Developer wrote:

Hi ,

I have situation where I get data from SRC Flat file and have to load Dimensional table and also fact table, using same data flow(have no other choice since I have to unpivot some src data). Since I have to load both tables in same data flow, I have to have a way to put load ordering constraint (I know informatica allows that). Does any one have any idea on how this can be done in SSIS?

I would be really grateful.

Thanks

Very very good question. The feature you are referring to is sometimes called "Intrinsic Flow Priority". It doesn't exist in SSIS at the moment and I hope to god they put it into the next release. I have requested it here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178058 and have noted that it exists in Informatica. I would appreicate it if you could click-through and add some comments. We're more likely to get it if more people ask for it and give real reasons why they need it.

In the meantime, you can achieve the same using raw files to pass data between different data-flows. This is explained here:

Splitting order detail and order header information from one file into multiple tables
(http://blogs.conchango.com/jamiethomson/archive/2006/05/22/3974.aspx)

HTH

-Jamie

Load a Dataset to SQL Mobile (Quickly)

I have five small tables that I need to insert to a SQL CE database.

I am using the 2.0 Compact Framework with the 2.0 System.Data.SqlServerCe.

My table definition is dynamic so I never know it's design.

1- If I go Row by Row using an this.ExecuteNonQuery(_global, par); it takes about 26 seconds to insert 5 tables of 330 rows.

2- If a use

StringBuilder sbColumns = new StringBuilder();

foreach (DataColumn dc in table.Columns)

{

if (sbColumns.ToString() != "")

sbColumns.Append(",");

sbColumns.Append(dc.ColumnName);

}

SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT " + sbColumns.ToString() + " FROM " + _tablename, m_con);

SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);

da.MissingMappingAction = MissingMappingAction.Passthrough;

da.InsertCommand = cb.GetInsertCommand();

da.Update(table);

da.Dispose();

it takes about 46 seconds.

How Can write it faster or is this fastest it can go?

Thanks

You can use SqlCeResultset, which will be the fastet option in .NET. see http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceresultset.aspx

You can find details in this excellent article by Joao: http://www.pocketpcdn.com/articles/articles.php?&atb.set(c_id)=74&atb.set(a_id)=11003&atb.perform(details)=&

Listing tables in a database

Rich,
Use view INFORMATION_SCHEMA.TABLES
use northwind
go
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
go
AMB
"Rich" wrote:

> Hello Group,
> how can I run a query in the QA to list the table names in a particular
> database?
> RichHello Mesa,
how can I add the creation date to the list?
Rich
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Rich,
> Use view INFORMATION_SCHEMA.TABLES
> use northwind
> go
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where TABLE_TYPE = 'BASE TABLE'
> go
>
> AMB
>
> "Rich" wrote:
>|||Hello Group,
how can I run a query in the QA to list the table names in a particular
database?
Rich|||Rich,
Use view INFORMATION_SCHEMA.TABLES
use northwind
go
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
go
AMB
"Rich" wrote:

> Hello Group,
> how can I run a query in the QA to list the table names in a particular
> database?
> Rich|||Hello Mesa,
how can I add the creation date to the list?
Rich
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Rich,
> Use view INFORMATION_SCHEMA.TABLES
> use northwind
> go
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where TABLE_TYPE = 'BASE TABLE'
> go
>
> AMB
>
> "Rich" wrote:
>|||Rich
To retrieve the create date of a table you will need to query the sysobjects
system table. The following example illustrates a query that returns the
table name and the creation date of the table:
USE northwind
GO
SELECT name, crdate
FROM dbo.sysobjects
WHERE xtype = 'U' -- User table
HTH
- Peter Ward
WARDY IT Solutions
"Rich" wrote:
[vbcol=seagreen]
> Hello Mesa,
> how can I add the creation date to the list?
> Rich
> "Alejandro Mesa" wrote:
>|||Or, if you're using SQL 2005, then also using the sys.objects Catalog
View
SELECT name, create_date
FROM sys.objects
WHERE type = 'U'|||Rich
To retrieve the create date of a table you will need to query the sysobjects
system table. The following example illustrates a query that returns the
table name and the creation date of the table:
USE northwind
GO
SELECT name, crdate
FROM dbo.sysobjects
WHERE xtype = 'U' -- User table
HTH
- Peter Ward
WARDY IT Solutions
"Rich" wrote:
[vbcol=seagreen]
> Hello Mesa,
> how can I add the creation date to the list?
> Rich
> "Alejandro Mesa" wrote:
>|||Or, if you're using SQL 2005, then also using the sys.objects Catalog
View
SELECT name, create_date
FROM sys.objects
WHERE type = 'U'

Listing tables in a database

Hello Group,
how can I run a query in the QA to list the table names in a particular
database?
RichRich,
Use view INFORMATION_SCHEMA.TABLES
use northwind
go
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
go
AMB
"Rich" wrote:
> Hello Group,
> how can I run a query in the QA to list the table names in a particular
> database?
> Rich|||Hello Mesa,
how can I add the creation date to the list?
Rich
"Alejandro Mesa" wrote:
> Rich,
> Use view INFORMATION_SCHEMA.TABLES
> use northwind
> go
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where TABLE_TYPE = 'BASE TABLE'
> go
>
> AMB
>
> "Rich" wrote:
> > Hello Group,
> >
> > how can I run a query in the QA to list the table names in a particular
> > database?
> >
> > Rich|||Rich
To retrieve the create date of a table you will need to query the sysobjects
system table. The following example illustrates a query that returns the
table name and the creation date of the table:
USE northwind
GO
SELECT name, crdate
FROM dbo.sysobjects
WHERE xtype = 'U' -- User table
HTH
- Peter Ward
WARDY IT Solutions
"Rich" wrote:
> Hello Mesa,
> how can I add the creation date to the list?
> Rich
> "Alejandro Mesa" wrote:
> > Rich,
> >
> > Use view INFORMATION_SCHEMA.TABLES
> >
> > use northwind
> > go
> >
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.TABLES
> > where TABLE_TYPE = 'BASE TABLE'
> > go
> >
> >
> > AMB
> >
> >
> > "Rich" wrote:
> >
> > > Hello Group,
> > >
> > > how can I run a query in the QA to list the table names in a particular
> > > database?
> > >
> > > Rich|||Or, if you're using SQL 2005, then also using the sys.objects Catalog
View
SELECT name, create_date
FROM sys.objects
WHERE type = 'U'

listing only owners with more than one car query help

Hi, here is the problem I have to resolve, I'll try to be very clear about the situation (using MS Access):

- I have 2 tables (well, I have more but only two are relevant to the query) one table stores owner information (owner #, name, surname, phone number...) and the other stores car info (car #, brand, type, color and owner #)

- I want to make a query that gives me the owner info but only for owners that have MORE than one car (ie 2 or more). I tried many approaches but without any success, so far..

- It doesn't matter if I get the same user information 2,3,4 times, I will take the query and build a report with it, and I'll group the cars by users.

Any ideas for me? This really bothers me because I really don't have a clue and I'm sure I missed something easy... :(I'd use:SELECT ownerID
FROM cars
GROUP BY ownerID
HAVING 1 < Count(*);

-PatP|||Thanks, Pat, this correctly lists people with more than one car :) But if I want to da a report with the car info for each car that each owner of more than 1 car (err.. I wanna do a report with the user info + the car info in a report, grouped by the user), I must have the info of each car also.. Your query works like I want, but only list the users and I can't add car info on one line for each car.. Well, I know that sounded pretty bad.. Any idea?

Thanks for the quick answer there, Pat!|||Picky, picky, picky! Ok, at least the logic is good. Now let's try:SELECT *
FROM cars AS a
WHERE 1 < (SELECT Count(*)
FROM cars AS z
WHERE z.ownerID = a.ownerID);...and see if that am more gooder yet even. (How many ways can I butcher the English language... Is there a finite limit?)

-PatP|||All I want to say is: "Thanks alot, this is exactly what I wanted.. I'll be trying to add informations about the owners from the owner's table now.. But this is exactly what I meant!

Thanks for this, Pat, you are a real pal!|||No problemo! Always glad to "stir the pot" any way I can.

-PatPsql

Listing foreign keys

Hi all,
I've got a couple of queries that I can't figure out exactly how to build
them. Both go over the 'sys' tables.
All my tables have the primarykey called 'id_no'. So if I have a table
'customers', the identity field is called 'id_no'. If I have a second table
'orders' with a foreign key on 'customers', the field is called
'id_no_customer'...an so on...
I need 2 queries:
1. The first should return a list of those columns (including source and
target tables) that are defined as foreignkeys (ie. columnname starts with
'id_no_') but no foreignkey actually exacts.
2. The second query should return a list of foreignkeys with the following
information:
target_table
target_column
source_table
source_column
I've tried the following, but on a table of 3 foreignkeys (1 is actually
missing), I get a result of 50, instead of just 3.
select target_table.name, target_column.name, source_table.name,
source_column.name from sysforeignkeys
inner join sysobjects target_table on sysforeignkeys.fkeyid =
target_table.id
inner join syscolumns target_column on sysforeignkeys.fkeyid =
target_column.id
inner join sysobjects source_table on sysforeignkeys.rkeyid =
source_table.id
inner join syscolumns source_column on sysforeignkeys.fkeyid =
source_column.id
where target_table.name = 'xxx'
Thanks for help!
IvanIvan
If I remember well , OJ had written this script
create procedure usp_findreferences
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
drop proc usp_findreferences
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:eWrQJySuFHA.3684@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I've got a couple of queries that I can't figure out exactly how to build
> them. Both go over the 'sys' tables.
> All my tables have the primarykey called 'id_no'. So if I have a table
> 'customers', the identity field is called 'id_no'. If I have a second
> table
> 'orders' with a foreign key on 'customers', the field is called
> 'id_no_customer'...an so on...
> I need 2 queries:
> 1. The first should return a list of those columns (including source and
> target tables) that are defined as foreignkeys (ie. columnname starts with
> 'id_no_') but no foreignkey actually exacts.
> 2. The second query should return a list of foreignkeys with the following
> information:
> target_table
> target_column
> source_table
> source_column
> I've tried the following, but on a table of 3 foreignkeys (1 is actually
> missing), I get a result of 50, instead of just 3.
> select target_table.name, target_column.name, source_table.name,
> source_column.name from sysforeignkeys
> inner join sysobjects target_table on sysforeignkeys.fkeyid =
> target_table.id
> inner join syscolumns target_column on sysforeignkeys.fkeyid =
> target_column.id
> inner join sysobjects source_table on sysforeignkeys.rkeyid =
> source_table.id
> inner join syscolumns source_column on sysforeignkeys.fkeyid =
> source_column.id
> where target_table.name = 'xxx'
> Thanks for help!
> Ivan
>|||I solved the 2nd query this way:
select target_table.name as target_table, target_column.name as
target_column,
source_table.name as source_table, source_column.name as source_column from
sysforeignkeys
inner join sysobjects target_table on sysforeignkeys.fkeyid =
target_table.id
inner join syscolumns target_column
on sysforeignkeys.fkeyid = target_column.id
and sysforeignkeys.fkey = target_column.colid
inner join sysobjects source_table on sysforeignkeys.rkeyid =
source_table.id
inner join syscolumns source_column
on sysforeignkeys.rkeyid = source_column.id
and sysforeignkeys.rkey = source_column.colid
where target_table.name = 'xxx'
But I now have to solve the first query.
Ivan
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:OUrX$1SuFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Ivan
> If I remember well , OJ had written this script
> create procedure usp_findreferences
> @.tbname sysname=null
> as
> set nocount on
>
> Print 'Referenced:'
> select c1.table_name,
> c1.column_name,
> fkey=r.constraint_name,
> referenced_parent_table=c2.table_name,
> c2.column_name
> from information_schema.constraint_column_usage c1 join
> information_schema.referential_constraints r on
> c1.constraint_name=r.constraint_name
> join information_schema.constraint_column_usage c2 on
> r.unique_constraint_name=c2.constraint_name
> where c1.table_name=coalesce(@.tbname,c1.table_name)
> order by case when @.tbname is null then c1.table_name else c2.table_name
end
>
> print ''
> print 'Referencing:'
> select c1.table_name,
> c1.column_name,
> fkey=r.constraint_name,
> referencing_child_table=c2.table_name,
> c2.column_name
> from information_schema.constraint_column_usage c1 join
> information_schema.referential_constraints r on
> c1.constraint_name=r.unique_constraint_name
> join information_schema.constraint_column_usage c2 on
> r.constraint_name=c2.constraint_name
> where c1.table_name=coalesce(@.tbname,c1.table_name)
> order by case when @.tbname is null then c1.table_name else c2.table_name
end
> go
>
> --test run
> exec usp_findreferences 'Orders'
> drop proc usp_findreferences
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eWrQJySuFHA.3684@.TK2MSFTNGP09.phx.gbl...
build
with
following
>

Listing Db values

Hi, I want to make a logfile where i store all tables, collnames and values of a specified database. Which statement can I use in SQLserver or Oracle? I already found the following statements:

Oracle:
select * from all_tables
select * from user_tables

SQLserver:
select * from sysobjects where type'='U'

So getting the tablenames isn't the problem. The question is how the get the matching columns with their type and value.

Tnx.try this one
select sysobjects.name as Table_Name,syscolumns.name as Column_Name,systypes.name as Data_Type from sysobjects
join syscolumns on sysobjects.id=syscolumns.id
join systypes on syscolumns.xtype=systypes.xtype and systypes.status=typestat
where sysobjects.type='u'

Originally posted by kixer
Hi, I want to make a logfile where i store all tables, collnames and values of a specified database. Which statement can I use in SQLserver or Oracle? I already found the following statements:

Oracle:
select * from all_tables
select * from user_tables

SQLserver:
select * from sysobjects where type'='U'

So getting the tablenames isn't the problem. The question is how the get the matching columns with their type and value.

Tnx.|||Thanx! ;) Now I know the objectnames. All I have to do now is to make a nice treeview with the generated values, so i can log some sort of a dictionary.

Listing all tables and their columns?

Hey all. I apologize, but I'm a developer, not a DBA. I need to run a query that will list each table in a DB as well as the columns i nthose tables.

I know that you can use: EXEC sp_help 'table_name' to get a description, but I'm not sure how to set up a cursor to substitute the table names, or where to get the tables names.

Any help would be greatly appreciated. Thanks!Select Column_name,table_name From Information_schema.columns|||excellent, thanks!sql

Liste Tables

bonjour,
Existe t il une stored proc qui permette de récupérer la liste des tables
d'une base de donnée Sql 2K ?
Et si oui peut on ensuite récupérer la structure d'une table ; liste de
champs, d'index, de déclencheurs, contraints...etc
Merci
Christophe.There are many ways, pl. search the achives of this newsgroup. Here is an
easy one:
EXEC sp_tables
For keys, you can do:
EXEC sp_pkey 'tbl'
--
- Anith
( Please reply to newsgroups only )|||Sorry for this 'french' post, and Thanks for help !
Regards
Christophe
"meynet" <meynet@.csprogramme.com> a écrit dans le message de
news:bjq00r$670$1@.news-reader4.wanadoo.fr...
> bonjour,
> Existe t il une stored proc qui permette de récupérer la liste des tables
> d'une base de donnée Sql 2K ?
> Et si oui peut on ensuite récupérer la structure d'une table ; liste de
> champs, d'index, de déclencheurs, contraints...etc
> Merci
> Christophe.
>|||merci beaucoup, Vishal !
--
- Anith
( Please reply to newsgroups only )|||This is a multi-part message in MIME format.
--=_NextPart_000_03B7_01C37881.C6A60830
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You should have left this one to Aaron then ;-)
-- Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uI4R99GeDHA.2236@.TK2MSFTNGP12.phx.gbl...
My French is weak but I think you need sp_help.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"meynet" <meynet@.csprogramme.com> wrote in message =news:bjq00r$670$1@.news-reader4.wanadoo.fr...
bonjour,
Existe t il une stored proc qui permette de r=E9cup=E9rer la liste des =tables
d'une base de donn=E9e Sql 2K ?
Et si oui peut on ensuite r=E9cup=E9rer la structure d'une table ; =liste de
champs, d'index, de d=E9clencheurs, contraints...etc
Merci
Christophe.
--=_NextPart_000_03B7_01C37881.C6A60830
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Dwindows-1252">
<META content=3D"MSHTML 6.00.2800.1226" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#c0c0c0>
<DIV><FONT face=3DArial size=3D2>You should have left this one to Aaron =then ;-)</FONT></DIV>
<DIV><BR>-- <BR>Jacco Schalkwijk MCDBA, MCSD, MCSE<BR>Database Administrator<BR>Eurostop Ltd.</DIV>
<DIV> </DIV>
<DIV> </DIV>
<BLOCKQUOTE style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Tom Moreau" <<A =href=3D"mailto:tom@.dont.spam.me.cips.ca">tom@.dont.spam.me.cips.ca</A>>= wrote in message <A =href=3D"news:uI4R99GeDHA.2236@.TK2MSFTNGP12.phx.gbl">news:uI4R99GeDHA.2236=@.TK2MSFTNGP12.phx.gbl</A>...</DIV>
<DIV><FONT face=3DTahoma size=3D2>My French is weak but I think you =need sp_help.</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </DIV>
=<DIV>---<BR>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =Server Professional<BR>Toronto, ON Canada<BR><A =href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"meynet" <<A href=3D"mailto:meynet@.csprogramme.com">meynet@.csprogramme.com</A>> =wrote in message <A =href=3D"news:bjq00r$670$1@.news-reader4.wanadoo.fr">news:bjq00r$670$1@.news=-reader4.wanadoo.fr</A>...</DIV>bonjour,<BR><BR>Existe t il une stored proc qui permette de r=E9cup=E9rer la liste des =tables<BR>d'une base de donn=E9e Sql 2K ?<BR>Et si oui peut on ensuite r=E9cup=E9rer =la structure d'une table ; liste de<BR>champs, d'index, de d=E9clencheurs, =contraints...etc<BR><BR>Merci<BR>Christophe.<BR><BR></BLOCKQUOTE></BODY><=/HTML>
--=_NextPart_000_03B7_01C37881.C6A60830--|||This is a multi-part message in MIME format.
--=_NextPart_000_02A5_01C37857.EFA1EBC0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Y'know, just 'cuz we're Canadian, does mean we speak French, eh? ;-)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message =news:OTFK4jHeDHA.3024@.tk2msftngp13.phx.gbl...
You should have left this one to Aaron then ;-)
-- Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uI4R99GeDHA.2236@.TK2MSFTNGP12.phx.gbl...
My French is weak but I think you need sp_help.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"meynet" <meynet@.csprogramme.com> wrote in message =news:bjq00r$670$1@.news-reader4.wanadoo.fr...
bonjour,
Existe t il une stored proc qui permette de r=E9cup=E9rer la liste des =tables
d'une base de donn=E9e Sql 2K ?
Et si oui peut on ensuite r=E9cup=E9rer la structure d'une table ; =liste de
champs, d'index, de d=E9clencheurs, contraints...etc
Merci
Christophe.
--=_NextPart_000_02A5_01C37857.EFA1EBC0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Dwindows-1252">
<META content=3D"MSHTML 6.00.2800.1226" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#c0c0c0>
<DIV><FONT face=3DTahoma size=3D2>Y'know, just 'cuz we're Canadian, does =mean we speak French, eh? ;-)</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </DIV>
<DIV>---<BR>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =Server Professional<BR>Toronto, ON Canada<BR><A href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"Jacco Schalkwijk" <<A href=3D"mailto:NOSPAMjaccos@.eurostop.co.uk">NOSPAMjaccos@.eurostop.co.uk</=A>> wrote in message <A href=3D"news:OTFK4jHeDHA.3024@.tk2msftngp13.phx.gbl">news:OTFK4jHeDHA.3024=@.tk2msftngp13.phx.gbl</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>You should have left this one to Aaron =then ;-)</FONT></DIV>
<DIV><BR>-- <BR>Jacco Schalkwijk MCDBA, MCSD, MCSE<BR>Database Administrator<BR>Eurostop Ltd.</DIV>
<DIV> </DIV>
<DIV> </DIV>
<BLOCKQUOTE style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Tom Moreau" <<A =href=3D"mailto:tom@.dont.spam.me.cips.ca">tom@.dont.spam.me.cips.ca</A>>= wrote in message <A =href=3D"news:uI4R99GeDHA.2236@.TK2MSFTNGP12.phx.gbl">news:uI4R99GeDHA.2236=@.TK2MSFTNGP12.phx.gbl</A>...</DIV>
<DIV><FONT face=3DTahoma size=3D2>My French is weak but I think you =need sp_help.</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </DIV>
=<DIV>---<BR>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =Server Professional<BR>Toronto, ON Canada<BR><A =href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"meynet" <<A href=3D"mailto:meynet@.csprogramme.com">meynet@.csprogramme.com</A>> =wrote in message <A =href=3D"news:bjq00r$670$1@.news-reader4.wanadoo.fr">news:bjq00r$670$1@.news=-reader4.wanadoo.fr</A>...</DIV>bonjour,<BR><BR>Existe t il une stored proc qui permette de r=E9cup=E9rer la liste des =tables<BR>d'une base de donn=E9e Sql 2K ?<BR>Et si oui peut on ensuite r=E9cup=E9rer =la structure d'une table ; liste de<BR>champs, d'index, de d=E9clencheurs, =contraints...etc<BR><BR>Merci<BR>Christophe.<BR><BR></BLOCKQUOTE></BODY><=/HTML>
--=_NextPart_000_02A5_01C37857.EFA1EBC0--|||de rien, je vous en prie
uhh, i've to take help this :-)
http://www.canuckabroad.com/language/french.shtml
Cheers,
-Vishal
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uhKjgiHeDHA.3024@.tk2msftngp13.phx.gbl...
> merci beaucoup, Vishal !
> --
> - Anith
> ( Please reply to newsgroups only )
>|||This is a multi-part message in MIME format.
--=_NextPart_000_02FD_01C37860.F8CD8D40
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
He's from North Bay, Ontario.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message =news:#oIPWpHeDHA.2268@.TK2MSFTNGP10.phx.gbl...
I thought Aaron is a French Canadian? (or should that be Quebecois?)
-- Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:e7GBqlHeDHA.2248@.TK2MSFTNGP09.phx.gbl...
Y'know, just 'cuz we're Canadian, does mean we speak French, eh? ;-)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message =news:OTFK4jHeDHA.3024@.tk2msftngp13.phx.gbl...
You should have left this one to Aaron then ;-)
-- Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uI4R99GeDHA.2236@.TK2MSFTNGP12.phx.gbl...
My French is weak but I think you need sp_help.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"meynet" <meynet@.csprogramme.com> wrote in message =news:bjq00r$670$1@.news-reader4.wanadoo.fr...
bonjour,
Existe t il une stored proc qui permette de r=E9cup=E9rer la liste =des tables
d'une base de donn=E9e Sql 2K ?
Et si oui peut on ensuite r=E9cup=E9rer la structure d'une table ; =liste de
champs, d'index, de d=E9clencheurs, contraints...etc
Merci
Christophe.
--=_NextPart_000_02FD_01C37860.F8CD8D40
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

He's from North Bay, =Ontario.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jacco Schalkwijk" wrote in message news:#oIPWpHeDHA.2268=@.TK2MSFTNGP10.phx.gbl...
I thought Aaron is a French Canadian? =(or should that be Quebecois?)
-- Jacco Schalkwijk MCDBA, MCSD, MCSEDatabase AdministratorEurostop Ltd.
"Tom Moreau" = wrote in message news:e7GBqlHeDHA.2248=@.TK2MSFTNGP09.phx.gbl...
Y'know, just 'cuz we're Canadian, =does mean we speak French, eh? ;-)
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Jacco Schalkwijk" wrote in message news:OTFK4jHeDHA.3024=@.tk2msftngp13.phx.gbl...
You should have left this one to =Aaron then ;-)
-- Jacco Schalkwijk MCDBA, MCSD, MCSEDatabase AdministratorEurostop Ltd.


"Tom Moreau" = wrote in message news:uI4R99GeDHA.2236=@.TK2MSFTNGP12.phx.gbl...
My French is weak but I think you =need sp_help.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"meynet" =wrote in message news:bjq00r$670$1@.news=-reader4.wanadoo.fr...bonjour,Existe t il une stored proc qui permette de r=E9cup=E9rer la liste des =tablesd'une base de donn=E9e Sql 2K ?Et si oui peut on ensuite r=E9cup=E9rer =la structure d'une table ; liste dechamps, d'index, de d=E9clencheurs, =contraints...etcMerciChristophe.

---

--=_NextPart_000_02FD_01C37860.F8CD8D40--sql

List user-defined objects

Hi, all. How does one list all the user-defined objects (tables, udf's,
udt's, stored procedures, and views) for a SQL Server 2000 db -- the ones
owned by dbo? Thanks.Check information schema views in BOL.
use yourDB
go
declare @.s sysname
set @.s = N'dbo'
select
table_name,
table_type
from
information_schema.tables
where
(table_type = 'base table' or table_type = 'view')
and table_schema = @.s
and objectproperty(object_id(table_schema + '.' + quotename(table_name)),
'IsMSShipped') = 0
select
*
from
information_schema.column_domain_usage
where
domain_schema = @.s
select
routine_name,
routine_type
from
information_schema.routines
where
(routine_type = 'procedure' or routine_type = 'function')
and routine_schema = @.s
go
AMB
"dw" wrote:

> Hi, all. How does one list all the user-defined objects (tables, udf's,
> udt's, stored procedures, and views) for a SQL Server 2000 db -- the ones
> owned by dbo? Thanks.
>
>|||Thank you, Alejandro. I didn't know what to look under -- now I know to
research information schema views. Thanks :)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:8AC22FB8-4D41-471E-9762-1E669A1D3BBB@.microsoft.com...
> Check information schema views in BOL.
> use yourDB
> go
> declare @.s sysname
> set @.s = N'dbo'
> select
> table_name,
> table_type
> from
> information_schema.tables
> where
> (table_type = 'base table' or table_type = 'view')
> and table_schema = @.s
> and objectproperty(object_id(table_schema + '.' + quotename(table_name)),
> 'IsMSShipped') = 0
> select
> *
> from
> information_schema.column_domain_usage
> where
> domain_schema = @.s
> select
> routine_name,
> routine_type
> from
> information_schema.routines
> where
> (routine_type = 'procedure' or routine_type = 'function')
> and routine_schema = @.s
> go
>
> AMB
>
> "dw" wrote:
>|||http://www.aspfaq.com/search.asp?q=schema%3A
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:em7WicTNFHA.1884@.TK2MSFTNGP15.phx.gbl...
> Thank you, Alejandro. I didn't know what to look under -- now I know to
> research information schema views. Thanks :)
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:8AC22FB8-4D41-471E-9762-1E669A1D3BBB@.microsoft.com...
quotename(table_name)),
ones
>

List tables used in a SP

Is there a way to list all tables used in a SP?
I have a very large DB that has a couple hunderd tables and I need to know
what tables are used by about 85 SP.
Thanks.
G.It's not precise, but check out sp_depends in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"George Lake" <gdlake@.gmail.com> wrote in message
news:%23QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
Is there a way to list all tables used in a SP?
I have a very large DB that has a couple hunderd tables and I need to know
what tables are used by about 85 SP.
Thanks.
G.|||I'd say sp_depends is less than precise. It's useless if it can't guarantee
to give you reliable info. SQL Server doesn't have any native support for
reliably tracking down this kind of dependencies.
Linchi
"Tom Moreau" wrote:

> It's not precise, but check out sp_depends in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "George Lake" <gdlake@.gmail.com> wrote in message
> news:%23QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.
>
>|||mmmm
any thrid party application that can do this?
The idea is to not "run" the SP, I cant have all the parameters for the SPs
Thanks.
G.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:65BE8D76-0C4B-40FD-9B83-F79BE2319259@.microsoft.com...
> I'd say sp_depends is less than precise. It's useless if it can't
> guarantee
> to give you reliable info. SQL Server doesn't have any native support for
> reliably tracking down this kind of dependencies.
> Linchi
> "Tom Moreau" wrote:
>|||You could do something like :
select all tables from the db put them in a temporary table ,
using : SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
and then create a cursor hitting
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%tablename%'
AND ROUTINE_TYPE='PROCEDURE'
create a a recordset of all tables used in a sp . With a bit of added logic
if a record exists add to recordset
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"George Lake" <gdlake@.gmail.com> wrote in message
news:#QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.
>|||Although there isn't anything built in, here is what I do to identify if a
single table is used in stored procedures...
Generate a script for all of your stored procedures, and put it to a single
file. Search that file for the name of the table you are concerned with.
If you really need to know all the tables that are used, check "script all
dependent objects" when you generate your script. Every table that is
referenced should have a "create table" entry in the script.
note: if sp_depends doesn't always find dependencies, it is possible that
this method will miss dependencies as well. I have not used it on very
large databases, so I don't know if it will catch everything or not.
"George Lake" <gdlake@.gmail.com> wrote in message
news:%23QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.
>|||George,
this is pretty untested, but please give it a go:
select routine_name, table_name from information_schema.tables
cross join information_schema.routines
where routine_definition like '%' + table_name + '%'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||This is what I did recently to tackle this problem.
1. I ran a trace on particular long job.
2. Saved the Trace to a table
3. Retrieved a list of unique stored procedure calls
4. Generated a script for all the stored procedures with dependencies
on
5. Executed the script in new database.
I wound up coming up with a list of 104 tables for the 27 sprocs using
this method.
Then taking it furthur I looped through all those tables in a cursor
and ran sp_spacedused on each table Inserting the output into another
table.
I now had comlete stats on the tables, spaced used for a particlular
complex run in my application.
Andy
George Lake wrote:
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.|||This is what I did recently to tackle this problem.
1. I ran a trace on particular long job.
2. Saved the Trace to a table
3. Retrieved a list of unique stored procedure calls
4. Generated a script for all the stored procedures with dependencies
on
5. Executed the script in new database.
I wound up coming up with a list of 104 tables for the 27 sprocs using
this method.
Then taking it furthur I looped through all those tables in a cursor
and ran sp_spacedused on each table Inserting the output into another
table.
I now had comlete stats on the tables, spaced used for a particlular
complex run in my application.
Andy
George Lake wrote:
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.|||Short of a rebust T-SQL parser, there is no reliable, generally-applicable,
and automated way of identifying the tables used in a stored procedure.
But here's what I'd do, and it should cover most of the cases.
1. Script out the SP
2. Get a complete list of all the tables in the database
3. Write a little regular expression to look for each of the table name on
the list in the SP script. Make sure to look for 'complete' string that is
delimited either with whitespace or special characters.
You can get false positives from this, such as matching a string in the
comments or quoted string. But again, it should be very close.
Linchi
"George Lake" wrote:

> mmmm
> any thrid party application that can do this?
> The idea is to not "run" the SP, I cant have all the parameters for the SP
s
> Thanks.
> G.
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:65BE8D76-0C4B-40FD-9B83-F79BE2319259@.microsoft.com...
>
>

List tables used in a SP

Is there a way to list all tables used in a SP?
I have a very large DB that has a couple hunderd tables and I need to know
what tables are used by about 85 SP.
Thanks.
G.It's not precise, but check out sp_depends in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"George Lake" <gdlake@.gmail.com> wrote in message
news:%23QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
Is there a way to list all tables used in a SP?
I have a very large DB that has a couple hunderd tables and I need to know
what tables are used by about 85 SP.
Thanks.
G.|||I'd say sp_depends is less than precise. It's useless if it can't guarantee
to give you reliable info. SQL Server doesn't have any native support for
reliably tracking down this kind of dependencies.
Linchi
"Tom Moreau" wrote:

> It's not precise, but check out sp_depends in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "George Lake" <gdlake@.gmail.com> wrote in message
> news:%23QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.
>
>|||mmmm
any thrid party application that can do this?
The idea is to not "run" the SP, I cant have all the parameters for the SPs
Thanks.
G.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:65BE8D76-0C4B-40FD-9B83-F79BE2319259@.microsoft.com...[vbcol=seagreen]
> I'd say sp_depends is less than precise. It's useless if it can't
> guarantee
> to give you reliable info. SQL Server doesn't have any native support for
> reliably tracking down this kind of dependencies.
> Linchi
> "Tom Moreau" wrote:
>|||You could do something like :
select all tables from the db put them in a temporary table ,
using : SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
and then create a cursor hitting
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%tablename%'
AND ROUTINE_TYPE='PROCEDURE'
create a a recordset of all tables used in a sp . With a bit of added logic
if a record exists add to recordset
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"George Lake" <gdlake@.gmail.com> wrote in message
news:#QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.
>|||Although there isn't anything built in, here is what I do to identify if a
single table is used in stored procedures...
Generate a script for all of your stored procedures, and put it to a single
file. Search that file for the name of the table you are concerned with.
If you really need to know all the tables that are used, check "script all
dependent objects" when you generate your script. Every table that is
referenced should have a "create table" entry in the script.
note: if sp_depends doesn't always find dependencies, it is possible that
this method will miss dependencies as well. I have not used it on very
large databases, so I don't know if it will catch everything or not.
"George Lake" <gdlake@.gmail.com> wrote in message
news:%23QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.
>|||George,
this is pretty untested, but please give it a go:
select routine_name, table_name from information_schema.tables
cross join information_schema.routines
where routine_definition like '%' + table_name + '%'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||This is what I did recently to tackle this problem.
1. I ran a trace on particular long job.
2. Saved the Trace to a table
3. Retrieved a list of unique stored procedure calls
4. Generated a script for all the stored procedures with dependencies
on
5. Executed the script in new database.
I wound up coming up with a list of 104 tables for the 27 sprocs using
this method.
Then taking it furthur I looped through all those tables in a cursor
and ran sp_spacedused on each table Inserting the output into another
table.
I now had comlete stats on the tables, spaced used for a particlular
complex run in my application.
Andy
George Lake wrote:
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.|||This is what I did recently to tackle this problem.
1. I ran a trace on particular long job.
2. Saved the Trace to a table
3. Retrieved a list of unique stored procedure calls
4. Generated a script for all the stored procedures with dependencies
on
5. Executed the script in new database.
I wound up coming up with a list of 104 tables for the 27 sprocs using
this method.
Then taking it furthur I looped through all those tables in a cursor
and ran sp_spacedused on each table Inserting the output into another
table.
I now had comlete stats on the tables, spaced used for a particlular
complex run in my application.
Andy
George Lake wrote:
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.|||Short of a rebust T-SQL parser, there is no reliable, generally-applicable,
and automated way of identifying the tables used in a stored procedure.
But here's what I'd do, and it should cover most of the cases.
1. Script out the SP
2. Get a complete list of all the tables in the database
3. Write a little regular expression to look for each of the table name on
the list in the SP script. Make sure to look for 'complete' string that is
delimited either with whitespace or special characters.
You can get false positives from this, such as matching a string in the
comments or quoted string. But again, it should be very close.
Linchi
"George Lake" wrote:

> mmmm
> any thrid party application that can do this?
> The idea is to not "run" the SP, I cant have all the parameters for the SP
s
> Thanks.
> G.
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:65BE8D76-0C4B-40FD-9B83-F79BE2319259@.microsoft.com...
>
>

List tables used in a SP

Is there a way to list all tables used in a SP?
I have a very large DB that has a couple hunderd tables and I need to know
what tables are used by about 85 SP.
Thanks.
G.It's not precise, but check out sp_depends in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"George Lake" <gdlake@.gmail.com> wrote in message
news:%23QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
Is there a way to list all tables used in a SP?
I have a very large DB that has a couple hunderd tables and I need to know
what tables are used by about 85 SP.
Thanks.
G.|||I'd say sp_depends is less than precise. It's useless if it can't guarantee
to give you reliable info. SQL Server doesn't have any native support for
reliably tracking down this kind of dependencies.
Linchi
"Tom Moreau" wrote:
> It's not precise, but check out sp_depends in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "George Lake" <gdlake@.gmail.com> wrote in message
> news:%23QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.
>
>|||mmmm
any thrid party application that can do this?
The idea is to not "run" the SP, I cant have all the parameters for the SPs
Thanks.
G.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:65BE8D76-0C4B-40FD-9B83-F79BE2319259@.microsoft.com...
> I'd say sp_depends is less than precise. It's useless if it can't
> guarantee
> to give you reliable info. SQL Server doesn't have any native support for
> reliably tracking down this kind of dependencies.
> Linchi
> "Tom Moreau" wrote:
>> It's not precise, but check out sp_depends in the BOL.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> ..
>> "George Lake" <gdlake@.gmail.com> wrote in message
>> news:%23QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
>> Is there a way to list all tables used in a SP?
>> I have a very large DB that has a couple hunderd tables and I need to
>> know
>> what tables are used by about 85 SP.
>> Thanks.
>> G.
>>|||You could do something like :
select all tables from the db put them in a temporary table ,
using : SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
and then create a cursor hitting
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%tablename%'
AND ROUTINE_TYPE='PROCEDURE'
create a a recordset of all tables used in a sp . With a bit of added logic
if a record exists add to recordset
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"George Lake" <gdlake@.gmail.com> wrote in message
news:#QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.
>|||Although there isn't anything built in, here is what I do to identify if a
single table is used in stored procedures...
Generate a script for all of your stored procedures, and put it to a single
file. Search that file for the name of the table you are concerned with.
If you really need to know all the tables that are used, check "script all
dependent objects" when you generate your script. Every table that is
referenced should have a "create table" entry in the script.
note: if sp_depends doesn't always find dependencies, it is possible that
this method will miss dependencies as well. I have not used it on very
large databases, so I don't know if it will catch everything or not.
"George Lake" <gdlake@.gmail.com> wrote in message
news:%23QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.
>|||George,
this is pretty untested, but please give it a go:
select routine_name, table_name from information_schema.tables
cross join information_schema.routines
where routine_definition like '%' + table_name + '%'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||This is what I did recently to tackle this problem.
1. I ran a trace on particular long job.
2. Saved the Trace to a table
3. Retrieved a list of unique stored procedure calls
4. Generated a script for all the stored procedures with dependencies
on
5. Executed the script in new database.
I wound up coming up with a list of 104 tables for the 27 sprocs using
this method.
Then taking it furthur I looped through all those tables in a cursor
and ran sp_spacedused on each table Inserting the output into another
table.
I now had comlete stats on the tables, spaced used for a particlular
complex run in my application.
Andy
George Lake wrote:
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.|||This is what I did recently to tackle this problem.
1. I ran a trace on particular long job.
2. Saved the Trace to a table
3. Retrieved a list of unique stored procedure calls
4. Generated a script for all the stored procedures with dependencies
on
5. Executed the script in new database.
I wound up coming up with a list of 104 tables for the 27 sprocs using
this method.
Then taking it furthur I looped through all those tables in a cursor
and ran sp_spacedused on each table Inserting the output into another
table.
I now had comlete stats on the tables, spaced used for a particlular
complex run in my application.
Andy
George Lake wrote:
> Is there a way to list all tables used in a SP?
> I have a very large DB that has a couple hunderd tables and I need to know
> what tables are used by about 85 SP.
> Thanks.
> G.|||Short of a rebust T-SQL parser, there is no reliable, generally-applicable,
and automated way of identifying the tables used in a stored procedure.
But here's what I'd do, and it should cover most of the cases.
1. Script out the SP
2. Get a complete list of all the tables in the database
3. Write a little regular expression to look for each of the table name on
the list in the SP script. Make sure to look for 'complete' string that is
delimited either with whitespace or special characters.
You can get false positives from this, such as matching a string in the
comments or quoted string. But again, it should be very close.
Linchi
"George Lake" wrote:
> mmmm
> any thrid party application that can do this?
> The idea is to not "run" the SP, I cant have all the parameters for the SPs
> Thanks.
> G.
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:65BE8D76-0C4B-40FD-9B83-F79BE2319259@.microsoft.com...
> > I'd say sp_depends is less than precise. It's useless if it can't
> > guarantee
> > to give you reliable info. SQL Server doesn't have any native support for
> > reliably tracking down this kind of dependencies.
> >
> > Linchi
> >
> > "Tom Moreau" wrote:
> >
> >> It's not precise, but check out sp_depends in the BOL.
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> SQL Server MVP
> >> Toronto, ON Canada
> >> ..
> >> "George Lake" <gdlake@.gmail.com> wrote in message
> >> news:%23QqHm5vYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> >> Is there a way to list all tables used in a SP?
> >> I have a very large DB that has a couple hunderd tables and I need to
> >> know
> >> what tables are used by about 85 SP.
> >>
> >> Thanks.
> >> G.
> >>
> >>
> >>
>
>

List Tables in a view?

Is there a way to easily list the tables/views that a view is using to get its data?
Thanks in advance,
Shawnsp_depends <Viewname>

Books online {sp_depends}

List Tables In A DataBase

hi everybody

If anybody knows how to list the tables in a database in Sql Server Query Analyser .

I would like to know is there any commands or SP 's like sp_help

Thank you for all the support

thanks & regards

Hi,

Simplest way would be to query the sysobjects table:

select name from sysobjects where xtype = 'U'

'U' = User table

Checkout sysobjects in BOL.

Jamie

|||

Another alternative is to use something more like:

SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA ='dbo'
AND TABLE_TYPE ='BASE TABLE'

The advantage of this is that it does not use the system objects which are subject to change.

|||

Hi, nevincm

Code Snippet

select * from sysobjects where xtype = 'u' /*This will list all tables and its properties*/
select name from sysobjects where xtype = 'u' /*This will list table name as Jamie mentioned*/

Check this code

list tables depending if column exists

Hi there,
Is there a quick way to list all the tables in a DB that contain a certain column name?
Thanks
Sselect name from sysobjects o
where exists (select 1 from information_schema.columns c
where o.name = c.table_name
and column_name like '%column_name_patter%')|||Thanks for this!!!!!|||Thanks again!

I've now got a list of all the tables containing a certain column name, but I'm trying to expand this further to narrow down the results to:

all tables containing columnX and where columnX >= 10000 and <= 20000

Any ideas how I can search on this criteria?

Thanks I'd appreciate any input or advice.

S|||I'll give you what you asked for... You'll have to decide if that is what you want (I suspect that it is not).SELECT o.name
FROM dbo.syscolumns AS c
INNER JOIN dbo.sysobjects AS o
ON (o.id = c.id)
WHERE c.name = 'whatever'
AND c.name BETWEEN '10000' AND '20000'What I suspect that you really want is to check the values of the column in the tables themselves to see if there are rows in the table with column values between 10000 and 20000. That is a slightly more challenging request.

-PatP|||select 'select * from ' + name + ' where ' + column_name + ' between 10000 and 20000' from sysobjects o inner join information_schema.columns c
on o.name = c.table_name
where column_name like '%column_name_patter%'

Then you can execute the resulting series of SELECT statements. Or you can always put it into a cursor and do "EXEC ('...statement...)" on each row.

List Tables Associated with Specific Partition Scheme

How do I list the tables associated with a particular partition scheme? Basically I need to join between sys.tables and sys.data_spaces. The sys.tables view has a field for lob_data_space_id, but this seems to apply to the large object fields.

-Darrell

select ps.name as SchemaName, obj.name as TableName from sys.partition_schemes ps

join sys.indexes idx on (ps.data_space_id=idx.data_space_id)

join sys.objects obj on (idx.object_id=obj.object_id)

|||

This tells me which indexes are partitioned on the partition scheme, but not exactly the tables. This only works for tables if you assume that each table has at least one index created on the same partition scheme as the table itself. I want to be able to deal with the scenario where the table may not have any indexes create on the same partition as the base table.

The partitioned table may have no indexes or it may have indexes that are partitioned on a different scheme.

-Darrell

List Tables Associated with Specific Partition Scheme

How do I list the tables associated with a particular partition scheme? Basically I need to join between sys.tables and sys.data_spaces. The sys.tables view has a field for lob_data_space_id, but this seems to apply to the large object fields.

-Darrell

select ps.name as SchemaName, obj.name as TableName from sys.partition_schemes ps

join sys.indexes idx on (ps.data_space_id=idx.data_space_id)

join sys.objects obj on (idx.object_id=obj.object_id)

|||

This tells me which indexes are partitioned on the partition scheme, but not exactly the tables. This only works for tables if you assume that each table has at least one index created on the same partition scheme as the table itself. I want to be able to deal with the scenario where the table may not have any indexes create on the same partition as the base table.

The partitioned table may have no indexes or it may have indexes that are partitioned on a different scheme.

-Darrell

List Table sizes in db by mb?

Is there a way to see the size in mb of each table in a db?
I am trying to troubleshoot a problem I am seeing. I have 2 dbs with the
same tables and very similar data in the tables, yet when I right click on
the db in SQL enterprise manager and select properties the total db mb on 1
is 45 times bigger in mb than the 2nd.
Thx,
Scott BuerkleySee if this helps:
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
Also see DBCC UPDATEUSAGE in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
Is there a way to see the size in mb of each table in a db?
I am trying to troubleshoot a problem I am seeing. I have 2 dbs with the
same tables and very similar data in the tables, yet when I right click on
the db in SQL enterprise manager and select properties the total db mb on 1
is 45 times bigger in mb than the 2nd.
Thx,
Scott Buerkley|||Hi Narayana,
I have used your 'sp_show_huge_tables' Sp and got
Row count Total space used (MB)
2026 -.09
1548 -2.07
on two of my tables. How is this possible '
Thanks.

>--Original Message--
>See if this helps:
>http://vyaskn.tripod.com/sp_show_biggest_tables.htm
>Also see DBCC UPDATEUSAGE in SQL Server Books Online.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in
message
>news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
>Is there a way to see the size in mb of each table in a
db?
>I am trying to troubleshoot a problem I am seeing. I
have 2 dbs with the
>same tables and very similar data in the tables, yet when
I right click on
>the db in SQL enterprise manager and select properties
the total db mb on 1
>is 45 times bigger in mb than the 2nd.
>Thx,
>Scott Buerkley
>
>.
>|||Most probably incorrect info in sysindexes. Did you try DBCC UPDATEUSAGE?
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:bfce01c3ecd2$672940f0$a501280a@.phx.gbl...
> Hi Narayana,
> I have used your 'sp_show_huge_tables' Sp and got
>
> Row count Total space used (MB)
> 2026 -.09
> 1548 -2.07
> on two of my tables. How is this possible '
> Thanks.
>
>
> message
> db?
> have 2 dbs with the
> I right click on
> the total db mb on 1|||Thank You!!
This worked.
Thx,
Scott Buerkley
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OvSsK4M7DHA.1636@.TK2MSFTNGP12.phx.gbl...
> See if this helps:
> http://vyaskn.tripod.com/sp_show_biggest_tables.htm
> Also see DBCC UPDATEUSAGE in SQL Server Books Online.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
> news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> Is there a way to see the size in mb of each table in a db?
> I am trying to troubleshoot a problem I am seeing. I have 2 dbs with the
> same tables and very similar data in the tables, yet when I right click on
> the db in SQL enterprise manager and select properties the total db mb on
1
> is 45 times bigger in mb than the 2nd.
> Thx,
> Scott Buerkley
>
>|||I got a listing of my table sizes and as it turns out none were that large.
I keep investigating and found that the Transaction log file is the file
that is huge. Does anyone have any suggestions on how to fix this? I do
not need to roll back any data. It is all test data so far.
Thx,
Scott Buerkley
"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> Is there a way to see the size in mb of each table in a db?
> I am trying to troubleshoot a problem I am seeing. I have 2 dbs with the
> same tables and very similar data in the tables, yet when I right click on
> the db in SQL enterprise manager and select properties the total db mb on
1
> is 45 times bigger in mb than the 2nd.
> Thx,
> Scott Buerkley
>|||Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
news:uFWhAaN7DHA.3804@.tk2msftngp13.phx.gbl...
> I got a listing of my table sizes and as it turns out none were that
large.
> I keep investigating and found that the Transaction log file is the file
> that is huge. Does anyone have any suggestions on how to fix this? I do
> not need to roll back any data. It is all test data so far.
> Thx,
> Scott Buerkley
> "Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
> news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
the
on
on
> 1
>|||This helped.
Thank You!!!
Scott Buerkley
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23wY299N7DHA.1460@.tk2msftngp13.phx.gbl...
> Check out below KB articles:
> INF: How to Shrink the SQL Server 7.0 Transaction Log
> http://support.microsoft.com/defaul...kb;en-us;256650
> INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
> http://support.microsoft.com/defaul...kb;en-us;272318
> Log File Grows too big
> http://www.support.microsoft.com/?id=317375
> Log file filling up
> http://www.support.microsoft.com/?id=110139
> Considerations for Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=315512
> http://www.mssqlserver.com/faq/logs-shrinklog.asp
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
> news:uFWhAaN7DHA.3804@.tk2msftngp13.phx.gbl...
> large.
do
> the
click
> on
> on
>

List Table sizes in db by mb?

Is there a way to see the size in mb of each table in a db?
I am trying to troubleshoot a problem I am seeing. I have 2 dbs with the
same tables and very similar data in the tables, yet when I right click on
the db in SQL enterprise manager and select properties the total db mb on 1
is 45 times bigger in mb than the 2nd.
Thx,
Scott BuerkleySee if this helps:
http://vyaskn.tripod.com/sp_show_biggest_tables.htm
Also see DBCC UPDATEUSAGE in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
Is there a way to see the size in mb of each table in a db?
I am trying to troubleshoot a problem I am seeing. I have 2 dbs with the
same tables and very similar data in the tables, yet when I right click on
the db in SQL enterprise manager and select properties the total db mb on 1
is 45 times bigger in mb than the 2nd.
Thx,
Scott Buerkley|||Hi Narayana,
I have used your 'sp_show_huge_tables' Sp and got
Row count Total space used (MB)
2026 -.09
1548 -2.07
on two of my tables. How is this possible '
Thanks.
>--Original Message--
>See if this helps:
>http://vyaskn.tripod.com/sp_show_biggest_tables.htm
>Also see DBCC UPDATEUSAGE in SQL Server Books Online.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in
message
>news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
>Is there a way to see the size in mb of each table in a
db?
>I am trying to troubleshoot a problem I am seeing. I
have 2 dbs with the
>same tables and very similar data in the tables, yet when
I right click on
>the db in SQL enterprise manager and select properties
the total db mb on 1
>is 45 times bigger in mb than the 2nd.
>Thx,
>Scott Buerkley
>
>.
>|||Most probably incorrect info in sysindexes. Did you try DBCC UPDATEUSAGE?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:bfce01c3ecd2$672940f0$a501280a@.phx.gbl...
> Hi Narayana,
> I have used your 'sp_show_huge_tables' Sp and got
>
> Row count Total space used (MB)
> 2026 -.09
> 1548 -2.07
> on two of my tables. How is this possible '
> Thanks.
>
> >--Original Message--
> >See if this helps:
> >http://vyaskn.tripod.com/sp_show_biggest_tables.htm
> >
> >Also see DBCC UPDATEUSAGE in SQL Server Books Online.
> >--
> >HTH,
> >Vyas, MVP (SQL Server)
> >http://vyaskn.tripod.com/
> >Is .NET important for a database professional?
> >http://vyaskn.tripod.com/poll.htm
> >
> >
> >"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in
> message
> >news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> >Is there a way to see the size in mb of each table in a
> db?
> >
> >I am trying to troubleshoot a problem I am seeing. I
> have 2 dbs with the
> >same tables and very similar data in the tables, yet when
> I right click on
> >the db in SQL enterprise manager and select properties
> the total db mb on 1
> >is 45 times bigger in mb than the 2nd.
> >
> >Thx,
> >Scott Buerkley
> >
> >
> >
> >.
> >|||Thank You!!
This worked.
Thx,
Scott Buerkley
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OvSsK4M7DHA.1636@.TK2MSFTNGP12.phx.gbl...
> See if this helps:
> http://vyaskn.tripod.com/sp_show_biggest_tables.htm
> Also see DBCC UPDATEUSAGE in SQL Server Books Online.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
> news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> Is there a way to see the size in mb of each table in a db?
> I am trying to troubleshoot a problem I am seeing. I have 2 dbs with the
> same tables and very similar data in the tables, yet when I right click on
> the db in SQL enterprise manager and select properties the total db mb on
1
> is 45 times bigger in mb than the 2nd.
> Thx,
> Scott Buerkley
>
>|||I got a listing of my table sizes and as it turns out none were that large.
I keep investigating and found that the Transaction log file is the file
that is huge. Does anyone have any suggestions on how to fix this? I do
not need to roll back any data. It is all test data so far.
Thx,
Scott Buerkley
"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> Is there a way to see the size in mb of each table in a db?
> I am trying to troubleshoot a problem I am seeing. I have 2 dbs with the
> same tables and very similar data in the tables, yet when I right click on
> the db in SQL enterprise manager and select properties the total db mb on
1
> is 45 times bigger in mb than the 2nd.
> Thx,
> Scott Buerkley
>|||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
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
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
"Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
news:uFWhAaN7DHA.3804@.tk2msftngp13.phx.gbl...
> I got a listing of my table sizes and as it turns out none were that
large.
> I keep investigating and found that the Transaction log file is the file
> that is huge. Does anyone have any suggestions on how to fix this? I do
> not need to roll back any data. It is all test data so far.
> Thx,
> Scott Buerkley
> "Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
> news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > Is there a way to see the size in mb of each table in a db?
> >
> > I am trying to troubleshoot a problem I am seeing. I have 2 dbs with
the
> > same tables and very similar data in the tables, yet when I right click
on
> > the db in SQL enterprise manager and select properties the total db mb
on
> 1
> > is 45 times bigger in mb than the 2nd.
> >
> > Thx,
> > Scott Buerkley
> >
> >
>|||This helped.
Thank You!!!
Scott Buerkley
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23wY299N7DHA.1460@.tk2msftngp13.phx.gbl...
> 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
> Log File Grows too big
> http://www.support.microsoft.com/?id=317375
> Log file filling up
> http://www.support.microsoft.com/?id=110139
> Considerations for Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=315512
> 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
>
> "Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
> news:uFWhAaN7DHA.3804@.tk2msftngp13.phx.gbl...
> > I got a listing of my table sizes and as it turns out none were that
> large.
> > I keep investigating and found that the Transaction log file is the file
> > that is huge. Does anyone have any suggestions on how to fix this? I
do
> > not need to roll back any data. It is all test data so far.
> >
> > Thx,
> > Scott Buerkley
> >
> > "Scott Buerkley" <Scott_Buerkley@.yahoo.com> wrote in message
> > news:%23N8T4uM7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > > Is there a way to see the size in mb of each table in a db?
> > >
> > > I am trying to troubleshoot a problem I am seeing. I have 2 dbs with
> the
> > > same tables and very similar data in the tables, yet when I right
click
> on
> > > the db in SQL enterprise manager and select properties the total db mb
> on
> > 1
> > > is 45 times bigger in mb than the 2nd.
> > >
> > > Thx,
> > > Scott Buerkley
> > >
> > >
> >
> >
>