Determine used fields/tables in database

Greetings to all!
I was curious if there was a way to determine what fields are in use by
stored procedures and/or views in a database. If there's a way through
SQL or through VB .NET, I would be most appreciative to learn about it.
Thanks,
Kyjan
"Kyjan" <HolySaphAngel@.gmail.com> wrote in message
news:1141065761.449583.226070@.u72g2000cwu.googlegr oups.com...
> Greetings to all!
> I was curious if there was a way to determine what fields are in use by
> stored procedures and/or views in a database. If there's a way through
> SQL or through VB .NET, I would be most appreciative to learn about it.
> Thanks,
> Kyjan
>
There may be an easier way, but you might look at how the sp_depends sproc
does it.
Rick Sawtell
MCT, MCSD, MCDBA
|||This way does not look at views. Is there a way to include those?
Kyjan
sql

Determine used fields/tables in database

Greetings to all!
I was curious if there was a way to determine what fields are in use by
stored procedures and/or views in a database. If there's a way through
SQL or through VB .NET, I would be most appreciative to learn about it.
Thanks,
Kyjan"Kyjan" <HolySaphAngel@.gmail.com> wrote in message
news:1141065761.449583.226070@.u72g2000cwu.googlegroups.com...
> Greetings to all!
> I was curious if there was a way to determine what fields are in use by
> stored procedures and/or views in a database. If there's a way through
> SQL or through VB .NET, I would be most appreciative to learn about it.
> Thanks,
> Kyjan
>
There may be an easier way, but you might look at how the sp_depends sproc
does it.
Rick Sawtell
MCT, MCSD, MCDBA|||This way does not look at views. Is there a way to include those?
Kyjan

Determine used fields/tables in database

Greetings to all!
I was curious if there was a way to determine what fields are in use by
stored procedures and/or views in a database. If there's a way through
SQL or through VB .NET, I would be most appreciative to learn about it.
Thanks,
Kyjan"Kyjan" <HolySaphAngel@.gmail.com> wrote in message
news:1141065761.449583.226070@.u72g2000cwu.googlegroups.com...
> Greetings to all!
> I was curious if there was a way to determine what fields are in use by
> stored procedures and/or views in a database. If there's a way through
> SQL or through VB .NET, I would be most appreciative to learn about it.
> Thanks,
> Kyjan
>
There may be an easier way, but you might look at how the sp_depends sproc
does it.
Rick Sawtell
MCT, MCSD, MCDBA|||This way does not look at views. Is there a way to include those?
Kyjan

Determine the size of an Image datatype

Is there any method to find out the size of an image datatype currently in the database. For example I have 2 tables that contain the same image, but the archive table is not displaying the same as the main. I am thinking the image file was corrupted somehow but don't know how to check this out.
Any ideas?DataLength() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_4ep4.asp)

-PatP

Determine the database objects' sizes?

I have a database whose size is over 50GB. What is the easy way to determine
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FLThis uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||FLX
1)sp_spaceused in the BOL
--
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
****************************************
************************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AN
D
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space u
sed (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric
(15,2),SUM(i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
--
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
****************************************
************************************[vbc
ol=seagreen]
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2
AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space
used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))[/vbcol]
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric
(15,2),SUM(i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =
0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL

> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>

Determine the database objects' sizes?

I have a database whose size is over 50GB. What is the easy way to determine
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FLThis uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||FLX
1)sp_spaceused in the BOL
--
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
****************************************************************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
--
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
****************************************************************************
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> > I have a database whose size is over 50GB. What is the easy way to
> determine
> > the size of each database objects (tables)? I want to find out the ones
> that
> > take most of the space. Thanks a lot,
> >
> > FL
> >
> >
> >
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> > I have a database whose size is over 50GB. What is the easy way to
> determine
> > the size of each database objects (tables)? I want to find out the ones
> that
> > take most of the space. Thanks a lot,
> >
> > FL
> >
> >
> >
>

Determine the database objects' sizes?

I have a database whose size is over 50GB. What is the easy way to determine
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FL
This uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>
|||FLX
1)sp_spaceused in the BOL
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
************************************************** **************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM (i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>
|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
************************************************** **************************
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM (i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =
0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL

> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>

determine tables that are Simple recovery

I need a sql statment to return a list of tables for a given database
where the Recovery Model option is Simple.
TIA
Rob"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1129311952.833612.171530@.g14g2000cwa.googlegr oups.com...
>I need a sql statment to return a list of tables for a given database
> where the Recovery Model option is Simple.
> TIA
> Rob

The recovery model is a database-wide setting. There is no recovery model
option for individual tables.

You can determine the recovery model like this:

SELECT DATABASEPROPERTYEX('database_name', 'RECOVERY')

--
David Portas
SQL Server MVP
--|||Better Question. How can it tell the databases in my sql instance that
have a recovery model of 'Simple'?
Hope this makes sense now
Thanks|||Erp! Yes, replace(question,'Tables','Database')
Thanks!|||"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1129328233.934256.231310@.g49g2000cwa.googlegr oups.com...
> Better Question. How can it tell the databases in my sql instance that
> have a recovery model of 'Simple'?
> Hope this makes sense now
> Thanks

SELECT catalog_name
FROM information_schema.schemata
WHERE DATABASEPROPERTYEX(catalog_name,'RECOVERY') = 'SIMPLE' ;

--
David Portas
SQL Server MVP
--|||Thank you very much! Ill be able to use this in my backup using SQLsafe
when I perform a log backup. I kept getting errors when it tried to
backup databases with simple model. Now I can skip them.

Determine Table's PK Columns

I want to do a query on a db's system tables to determine what column(s) are
part of the primary key.
Example.
Code:
SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
sysobjects.name='tablename'
Yields all the column names. But I just want to see those that are part of
the primary key. How?
Ryan
> SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
> sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
> sysobjects.name='tablename'
shuldn't you use information_schema?
.~. Might, Courage, Vision. Sincerity. http://www.linux-sxs.org
/ v \
/( _ )\ (Ubuntu 5.10) Linux 2.6.14.3
^ ^ 22:37:07 up 9 days 2:31 load average: 3.12 1.63 0.80|||Try:
select
*
from
information_schema.constraint_column_usage
where
table_name = 'Order Details'
and objectproperty (object_id (constraint_name), 'IsPrimaryKey') = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:037F8848-C843-4A77-B385-6994E6235CDA@.microsoft.com...
>I want to do a query on a db's system tables to determine what column(s)
>are
> part of the primary key.
> Example.
> Code:
> SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
> sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
> sysobjects.name='tablename'
> Yields all the column names. But I just want to see those that are part of
> the primary key. How?
> Ryan|||Thank you both of you. Yes, Man-wai Chang, the information_schema looks lik
e
it's a lot easier than joining the system tables like I was doing. Thank
you, both.

Determine table size

Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica
Hi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:

> Hi,
> I have a client with a database that is running over 1G in size. Looking at
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>
|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica
|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>
|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...[vbcol=seagreen]
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>

Determine table size

Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
MicaHi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:
> Hi,
> I have a client with a database that is running over 1G in size. Looking at
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
>> Hi,
>> I have a client with a database that is running over 1G in size. Looking
>> at
>> the tables, I can't find any that would seem to be larger than a 1-2 meg
>> with total size should be around 30meg. Is there any way to find out
>> table
>> sizes?
>> Thanks,
>> Mica
>>|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a client with a database that is running over 1G in size. Looking
>> at the tables, I can't find any that would seem to be larger than a 1-2
>> meg with total size should be around 30meg. Is there any way to find out
>> table sizes?
>> Thanks,
>> Mica
>sql

Determine table size

Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
MicaHi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:

> Hi,
> I have a client with a database that is running over 1G in size. Looking a
t
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...[vbcol=seagreen]
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
>|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>

Determine table names and column names at runtime?

Hi

I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.

Thanks.The only advice I can give is this: All your database's objects are stored in the sysobjects table. And then there's syscolumns and sysindexes (shouldn't that be sysindices?).

I would be suprised if there were not free libraries out there that make it easy to get information about sql objects. I don't know of one particularly. Anyone know of one? If not, that might make a cool community project.|||There is a stored procedure sp_tables that returns a list of tables.

There is a stored procedure sp_columns that returns a list of Columns.

These are better to use than the system tables, as they are documented and should not change in a way to break your code. Look them up in Books Online...|||Gravy!

Glad you're here.|||> There is a stored procedure sp_tables that returns a list of tables.

ah, forgot about that. I guess because I usually only use it to do an

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'whatever')

and an SP is no good there.... cheers for reminding me.|||In that case, you could/should use:

IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_name = 'whatever')

Terri

Determine Report Permissions with T-SQL

Is there a way, using the system tables in the ReportServer or
ReportServerTempDB to determine which Active Directory groups have access to
which reports. I want to document what I would otherwise have to go
report-by-report in the Report Manager screen to see. I've looked at
several of the system tables (Users, DataSource, Policies, Roles,
ConfigurationInfo, Catalog) but have had no luck.You need to use the GetPolicies and SetPolicies methods on the web service.
You don't want to do anything directly to the database.
Here's a code snippet giving you an idea of how to use these methods:
private bool AddUserToFolderPolicy(string folder, string user, ref string
errMessage)
{
try
{
//Get the Browser role
Role[]roles = m_ReportingService.ListRoles();
Role browserRole = new Role();
foreach (Role r in roles)
{
if (r.Name == "Browser") browserRole = r;
break;
}
Role[] policyRoles = new Role[1];
policyRoles[0] = new Role();
policyRoles[0] =browserRole;
//Get the current policies of the folder in question
string path = "/" + folder;
bool inheritParent = false;
Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
inheritParent);
//If the user is currently in the current policy set just return
for(int i=0;i<currentPolicies.Length;i++)
if(currentPolicies[i].GroupUserName == user)
return true;
//Create the new policy array and add the new user
ArrayList arrPolicies = new ArrayList(currentPolicies);
Policy p = new Policy();
p.GroupUserName = user;
p.Roles = policyRoles;
arrPolicies.Add(p);
Policy[] finalPolicies = (Policy[])arrPolicies.ToArray(typeof(Policy));
//Set the policies
m_ReportingService.SetPolicies(path,finalPolicies);
}
catch (Exception e)
{
errMessage = e.Message;
return false;
}
return true;
}
Adrian M.
MCP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
> Is there a way, using the system tables in the ReportServer or
> ReportServerTempDB to determine which Active Directory groups have access
> to
> which reports. I want to document what I would otherwise have to go
> report-by-report in the Report Manager screen to see. I've looked at
> several of the system tables (Users, DataSource, Policies, Roles,
> ConfigurationInfo, Catalog) but have had no luck.
>|||Adrian, thank you for the prompt reply. Unfortunately, I am not a C#
developer and need to accomplish this task in T-SQL. I don't really want to
"do" anything to the tables, I just want to "get" something from them, just
as I would a system table in master or anywhere else in SQL Server. Does
anyone ([MSFT] people perhaps?) know if this is possible or if there is any
documentation on how to navigate these tables?
"Adrian M." wrote:
> You need to use the GetPolicies and SetPolicies methods on the web service.
> You don't want to do anything directly to the database.
> Here's a code snippet giving you an idea of how to use these methods:
> private bool AddUserToFolderPolicy(string folder, string user, ref string
> errMessage)
> {
> try
> {
> //Get the Browser role
> Role[]roles = m_ReportingService.ListRoles();
> Role browserRole = new Role();
> foreach (Role r in roles)
> {
> if (r.Name == "Browser") browserRole = r;
> break;
> }
> Role[] policyRoles = new Role[1];
> policyRoles[0] = new Role();
> policyRoles[0] =browserRole;
> //Get the current policies of the folder in question
> string path = "/" + folder;
> bool inheritParent = false;
> Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
> inheritParent);
> //If the user is currently in the current policy set just return
> for(int i=0;i<currentPolicies.Length;i++)
> if(currentPolicies[i].GroupUserName == user)
> return true;
> //Create the new policy array and add the new user
> ArrayList arrPolicies = new ArrayList(currentPolicies);
> Policy p = new Policy();
> p.GroupUserName = user;
> p.Roles = policyRoles;
> arrPolicies.Add(p);
> Policy[] finalPolicies = (Policy[])arrPolicies.ToArray(typeof(Policy));
> //Set the policies
> m_ReportingService.SetPolicies(path,finalPolicies);
> }
> catch (Exception e)
> {
> errMessage = e.Message;
> return false;
> }
> return true;
> }
>
> --
> Adrian M.
> MCP
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
> > Is there a way, using the system tables in the ReportServer or
> > ReportServerTempDB to determine which Active Directory groups have access
> > to
> > which reports. I want to document what I would otherwise have to go
> > report-by-report in the Report Manager screen to see. I've looked at
> > several of the system tables (Users, DataSource, Policies, Roles,
> > ConfigurationInfo, Catalog) but have had no luck.
> >
>
>|||Microsoft doesn't support directly access to the Report Server DB (including
queries). Supported access is through the tools provided (Report Manager,
web service, etc...)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_dbadmin_v1_4915.asp
--
Adrian M.
MCP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:CDD9B6BC-38D9-400C-B905-D184949D0E91@.microsoft.com...
> Adrian, thank you for the prompt reply. Unfortunately, I am not a C#
> developer and need to accomplish this task in T-SQL. I don't really want
> to
> "do" anything to the tables, I just want to "get" something from them,
> just
> as I would a system table in master or anywhere else in SQL Server. Does
> anyone ([MSFT] people perhaps?) know if this is possible or if there is
> any
> documentation on how to navigate these tables?
> "Adrian M." wrote:
>> You need to use the GetPolicies and SetPolicies methods on the web
>> service.
>> You don't want to do anything directly to the database.
>> Here's a code snippet giving you an idea of how to use these methods:
>> private bool AddUserToFolderPolicy(string folder, string user, ref
>> string
>> errMessage)
>> {
>> try
>> {
>> //Get the Browser role
>> Role[]roles = m_ReportingService.ListRoles();
>> Role browserRole = new Role();
>> foreach (Role r in roles)
>> {
>> if (r.Name == "Browser") browserRole = r;
>> break;
>> }
>> Role[] policyRoles = new Role[1];
>> policyRoles[0] = new Role();
>> policyRoles[0] =browserRole;
>> //Get the current policies of the folder in question
>> string path = "/" + folder;
>> bool inheritParent = false;
>> Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
>> inheritParent);
>> //If the user is currently in the current policy set just return
>> for(int i=0;i<currentPolicies.Length;i++)
>> if(currentPolicies[i].GroupUserName == user)
>> return true;
>> //Create the new policy array and add the new user
>> ArrayList arrPolicies = new ArrayList(currentPolicies);
>> Policy p = new Policy();
>> p.GroupUserName = user;
>> p.Roles = policyRoles;
>> arrPolicies.Add(p);
>> Policy[] finalPolicies =>> (Policy[])arrPolicies.ToArray(typeof(Policy));
>> //Set the policies
>> m_ReportingService.SetPolicies(path,finalPolicies);
>> }
>> catch (Exception e)
>> {
>> errMessage = e.Message;
>> return false;
>> }
>> return true;
>> }
>>
>> --
>> Adrian M.
>> MCP
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
>> > Is there a way, using the system tables in the ReportServer or
>> > ReportServerTempDB to determine which Active Directory groups have
>> > access
>> > to
>> > which reports. I want to document what I would otherwise have to go
>> > report-by-report in the Report Manager screen to see. I've looked at
>> > several of the system tables (Users, DataSource, Policies, Roles,
>> > ConfigurationInfo, Catalog) but have had no luck.
>> >
>>|||Even if we were to document the tables, there is no way to use TSQL to get
this (without some extended SPs). We use Windows APIs to resolve group
membership and determine effective permissions from the ACL we store in the
database.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:CDD9B6BC-38D9-400C-B905-D184949D0E91@.microsoft.com...
> Adrian, thank you for the prompt reply. Unfortunately, I am not a C#
> developer and need to accomplish this task in T-SQL. I don't really want
> to
> "do" anything to the tables, I just want to "get" something from them,
> just
> as I would a system table in master or anywhere else in SQL Server. Does
> anyone ([MSFT] people perhaps?) know if this is possible or if there is
> any
> documentation on how to navigate these tables?
> "Adrian M." wrote:
>> You need to use the GetPolicies and SetPolicies methods on the web
>> service.
>> You don't want to do anything directly to the database.
>> Here's a code snippet giving you an idea of how to use these methods:
>> private bool AddUserToFolderPolicy(string folder, string user, ref
>> string
>> errMessage)
>> {
>> try
>> {
>> //Get the Browser role
>> Role[]roles = m_ReportingService.ListRoles();
>> Role browserRole = new Role();
>> foreach (Role r in roles)
>> {
>> if (r.Name == "Browser") browserRole = r;
>> break;
>> }
>> Role[] policyRoles = new Role[1];
>> policyRoles[0] = new Role();
>> policyRoles[0] =browserRole;
>> //Get the current policies of the folder in question
>> string path = "/" + folder;
>> bool inheritParent = false;
>> Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
>> inheritParent);
>> //If the user is currently in the current policy set just return
>> for(int i=0;i<currentPolicies.Length;i++)
>> if(currentPolicies[i].GroupUserName == user)
>> return true;
>> //Create the new policy array and add the new user
>> ArrayList arrPolicies = new ArrayList(currentPolicies);
>> Policy p = new Policy();
>> p.GroupUserName = user;
>> p.Roles = policyRoles;
>> arrPolicies.Add(p);
>> Policy[] finalPolicies =>> (Policy[])arrPolicies.ToArray(typeof(Policy));
>> //Set the policies
>> m_ReportingService.SetPolicies(path,finalPolicies);
>> }
>> catch (Exception e)
>> {
>> errMessage = e.Message;
>> return false;
>> }
>> return true;
>> }
>>
>> --
>> Adrian M.
>> MCP
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
>> > Is there a way, using the system tables in the ReportServer or
>> > ReportServerTempDB to determine which Active Directory groups have
>> > access
>> > to
>> > which reports. I want to document what I would otherwise have to go
>> > report-by-report in the Report Manager screen to see. I've looked at
>> > several of the system tables (Users, DataSource, Policies, Roles,
>> > ConfigurationInfo, Catalog) but have had no luck.
>> >
>>

Detecting Tables with no recent activity

Is there an efficent way to determine which tables in a SQL Server database
have had no activity reccently. This includes inserts, updates, deleted
and/or selects.
Are there any tools or techniques to find these tables?
All help appreciated!
Tim O wrote:
> Is there an efficent way to determine which tables in a SQL Server
> database have had no activity reccently. This includes inserts,
> updates, deleted and/or selects.
> Are there any tools or techniques to find these tables?
> All help appreciated!
Not inherently, as far as I know. The system tables do not track changes
to underlying objects. You can easily implement some very simple
triggers on the tables you are trying to track using something like the
following. This implementation tracks changes to a table at most once
each day. Using the Ignore_dup_key option on the clustered index tells
SQL Server to ignore duplicates, preventing you from having to manually
check the table each time. You could, of course, improve on this concept
and add a user column and add the suser_sname() value to it and the
index.
CREATE TABLE [testing] (
[col1] [int] NULL)
go
create table TableAccess(TblNm nvarchar(128) NOT NULL, AccessDate
char(8) NOT NULL)
go
create unique clustered index TableAccess_idx on TableAccess (TblNm,
AccessDate) with ignore_dup_key
go
create trigger testing_change on testing
for insert, update, delete
as
Begin
insert into TableAccess (TblNm, AccessDate) Values ('testing',
CONVERT(char(8), getdate(), 112))
End
go
insert into testing values (5)
insert into testing values (6)
insert into testing values (7)
David Gugick
Imceda Software
www.imceda.com
|||"Tim O" wrote:

> Is there an efficent way to determine which tables in a SQL Server database
> have had no activity reccently. This includes inserts, updates, deleted
> and/or selects.
> Are there any tools or techniques to find these tables?
> All help appreciated!
The use of TRIGGERS will not detect SELECTS that are executed on a TABLE as
far as I know!
I need an EFFICINIENT WAY to detect table that are neither read for written
to.
I am also concerned with the overheads that may be placed on the system.
There are a large number of tables and a high degree of daily activity!
Cheers Tim O
|||Tim O wrote:
> The use of TRIGGERS will not detect SELECTS that are executed on a
> TABLE as far as I know!
> I need an EFFICINIENT WAY to detect table that are neither read for
> written to.
> I am also concerned with the overheads that may be placed on the
> system. There are a large number of tables and a high degree of daily
> activity!
> Cheers Tim O
If you are using stored procedures, you can log access from there, even
on SELECTs. The overhead is minimal on insert activity. You can place
the table on a secondary filegroup or in another database, if necessary.
David Gugick
Imceda Software
www.imceda.com
|||Tim,
There is a column in sysindexes for the object that keeps a count of
modifications to the table. The column is rowmodctr. SQL uses this to keep
track of when to run Update Stats when "Auto Update Stats" is on. If after
running update stats for that table, the value rises above 0, then there are
modifications to it. For more info please see:
http://support.microsoft.com/default...en-us%3B195565
As far as Selects, you can run profiler (trace) to see if it is called using
the Filter "TextData". You can also see if it is embedded in any stored
procs, triggers via sp_depends <table_name>. This won't indicate any
references from outside the current DB, tho.
Hope some of this helps.
Steve
"Tim O" <TimO@.discussions.microsoft.com> wrote in message
news:31F8C451-62F7-480F-9362-736E07698FFF@.microsoft.com...[vbcol=seagreen]
>
> "Tim O" wrote:
database
> The use of TRIGGERS will not detect SELECTS that are executed on a TABLE
as
> far as I know!
> I need an EFFICINIENT WAY to detect table that are neither read for
written
> to.
> I am also concerned with the overheads that may be placed on the system.
> There are a large number of tables and a high degree of daily activity!
> Cheers Tim O

Detect Stored Procedure that refer to tables that do not exist

It is possible to construct a query that will list all stored procedures
that refer to tables that no longer exist in the database?
Thanks,
ChrisHi
Execute each SP with "SET NOEXEC ON" set.
From BOL
"The execution of statements in SQL Server consists of two phases:
compilation and execution. This setting is useful for having SQL Server
validate the syntax and object names in Transact-SQL code when executing. It
is also useful for debugging statements that would usually be part of a
larger batch of statements"
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"C-W" <nomailplease@.microsoft.nospam> wrote in message
news:eFzSEi3lFHA.3900@.TK2MSFTNGP09.phx.gbl...
> It is possible to construct a query that will list all stored procedures
> that refer to tables that no longer exist in the database?
>
> Thanks,
> Chris
>|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Execute each SP with "SET NOEXEC ON" set.
> From BOL
> "The execution of statements in SQL Server consists of two phases:
> compilation and execution. This setting is useful for having SQL
> Server validate the syntax and object names in Transact-SQL code when
> executing. It is also useful for debugging statements that would
> usually be part of a larger batch of statements"
> Regards
Mike,
NOEXEC doesn't seem to work for me. For some reason, it doesn't detect
the drop of the table in my example. However, when I tried using SET
FMTONLY ON, it worked and does not depend on any branching in the
procedure itself that would prevent access to the underlying missing
table.
create table abc123 (co1l int)
go
drop proc abc123test
go
create proc abc123test @.b bit
as
begin
if @.b = 0
Select co1l from abc123
else
Select id from sysobjects
end
go
exec abc123test 1
exec abc123test 0
drop table abc123
go
set fmtonly on
exec abc123test 1
exec abc123test 0
set fmtonly off
drop proc abc123test
go
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks,
Is it possible to perform the checking at the time the stored procedure is
created. The problem I have got is that it would take a long time to test a
thousand+ number of stored procedures using exec, supplying different
parameters to each one etc. So I thought it would be easier to generate a
script that attempts to drop and then recreate the stored procedures and
stop when it fails because the table does not exist.
So is it possible to perform the check when I actually do the CREATE PROC...
create proc abc123test @.b bit
as
Select co1l from abc123
go
Thanks,
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eG73N$3lFHA.2852@.TK2MSFTNGP15.phx.gbl...
> NOEXEC doesn't seem to work for me. For some reason, it doesn't detect the
> drop of the table in my example. However, when I tried using SET FMTONLY
> ON, it worked and does not depend on any branching in the procedure itself
> that would prevent access to the underlying missing table.
> create table abc123 (co1l int)
> go
> drop proc abc123test
> go
> create proc abc123test @.b bit
> as
> begin
> if @.b = 0
> Select co1l from abc123
> else
> Select id from sysobjects
> end
> go
> exec abc123test 1
> exec abc123test 0
> drop table abc123
> go
> set fmtonly on
> exec abc123test 1
> exec abc123test 0
> set fmtonly off
> drop proc abc123test
> go
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||C-W wrote:
> Thanks,
> Is it possible to perform the checking at the time the stored
> procedure is created. The problem I have got is that it would take a
> long time to test a thousand+ number of stored procedures using exec,
> supplying different parameters to each one etc. So I thought it
> would be easier to generate a script that attempts to drop and then
> recreate the stored procedures and stop when it fails because the
> table does not exist.
> So is it possible to perform the check when I actually do the CREATE
> PROC...
> create proc abc123test @.b bit
> as
> Select co1l from abc123
> go
>
> Thanks,
> Chris
>
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:eG73N$3lFHA.2852@.TK2MSFTNGP15.phx.gbl...
No. You can't do that at creation time. You could probably write
something pretty quickly to iterate through the syscolumns table for
each procedure and generate a dummy set of parameters. There is also
likely sample code out there to do the same.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks David
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23MGh30DmFHA.1372@.TK2MSFTNGP10.phx.gbl...
> No. You can't do that at creation time. You could probably write something
> pretty quickly to iterate through the syscolumns table for each procedure
> and generate a dummy set of parameters. There is also likely sample code
> out there to do the same.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||C-W wrote:
> Thanks David
> Chris
>
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:%23MGh30DmFHA.1372@.TK2MSFTNGP10.phx.gbl...
Ok. I whipped something together that you can use directly in a T-SQL
script. The script is mildly tested, so additional testing is required
at your end before using on a production system. The script creates the
necessary dynamic SQL to execute each procedure with NULL parameters and
then executes using SET FMTONLY ON and stored the results of the
execution in the temp table.
Good luck.
-- Stores the objects, execution SQL, and result
Create Table #ExecProcs (
ID INT IDENTITY NOT NULL,
Owner nvarchar(128) NOT NULL,
SPName nvarchar(128) NOT NULL,
ParameterCount INT NOT NULL,
ExecResult INT NULL,
ExecSQL nvarchar(2000) NOT NULL,
PRIMARY KEY (ID))
go
-- Get a list of procedures and create the execution SQL
Insert into #ExecProcs (
Owner,
SPName,
ParameterCount,
ExecSQL )
Select
USER_NAME(o.uid) as "Owner",
o.name as "SPName",
COUNT(p.name) as "ParameterCount",
'SET FMTONLY ON;' +
'EXEC [' +
USER_NAME(o.uid) + '].[' + o.name + '] ' +
CASE SIGN(COUNT(p.name))
WHEN 0
THEN ''
WHEN 1
THEN
REPLICATE('NULL,', COUNT(p.name) - 1) +
REPLICATE('NULL', COUNT(p.name) - (COUNT(p.name) - 1))
END + ';'
from
dbo.sysobjects o
left outer join
dbo.syscolumns p
on
o.id = p.id
where
o.type = 'P'
Group By
o.uid,
o.id,
o.name
Order By
o.uid,
o.id,
o.name
go
-- check the results
Select * from #ExecProcs
go
-- script to execute each procedure and store result in temp table
Declare @.Result INT
Declare @.SQL nvarchar(2000)
Declare @.ID INT
Declare @.Loop BIT
Set @.Loop = 1
While (@.Loop = 1)
Begin
SET @.ID = NULL
Select TOP 1
@.ID = ID,
@.SQL = ExecSQL
From
#ExecProcs
Where
ExecResult IS NULL
If @.ID IS NULL
SET @.Loop = 0
Else Begin
EXEC (@.SQL)
Update
#ExecProcs
Set
ExecResult = @.@.ERROR
Where
ID = @.ID
End
End
go
-- check results
Select * from #ExecProcs
go
-- drop the temp table
drop Table #ExecProcs
go
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David - this works great.
Thanks,
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:OuJKWnEmFHA.2904@.TK2MSFTNGP14.phx.gbl...
> C-W wrote:
> Ok. I whipped something together that you can use directly in a T-SQL
> script. The script is mildly tested, so additional testing is required at
> your end before using on a production system. The script creates the
> necessary dynamic SQL to execute each procedure with NULL parameters and
> then executes using SET FMTONLY ON and stored the results of the execution
> in the temp table.
> Good luck.

Detail information within other detail (different table)

I have a one page report that prints information from several tables. The
report contains a list object slightly smaller than the body size (8X10.5)
and a rectangle object slightly smaller than the list object. The rectangle
object contains 130 plus textboxes for labels and actual fields to be
printed. This is a profile type report used to print all information for a
member (name, address, city, state, and several other fields) and requires
the whole page. There can be further detail linked to this member. In this
case, committees that the member serves on. I can't figure out how to get the
top 5 committees to print with all of the other detail. Does anyone know a
"best method" to accomplish this?
Thanks,
JohnNevermind. Finally used a subreport which solved the problem very nicely.
John
"John Joslin" wrote:
> I have a one page report that prints information from several tables. The
> report contains a list object slightly smaller than the body size (8X10.5)
> and a rectangle object slightly smaller than the list object. The rectangle
> object contains 130 plus textboxes for labels and actual fields to be
> printed. This is a profile type report used to print all information for a
> member (name, address, city, state, and several other fields) and requires
> the whole page. There can be further detail linked to this member. In this
> case, committees that the member serves on. I can't figure out how to get the
> top 5 committees to print with all of the other detail. Does anyone know a
> "best method" to accomplish this?
> Thanks,
> John

Desperate for help.......

I want to incorporate fields from 2 tables in a single crystal report.I have no problems in incorporating from a single table. But when it comes to >1 table fields in a single report, I am stuck up..

For eg.:

Table 1 :"GENINFO"
-------
custno | mail id
--------

Table 2 :"DETAILS"
--------
custno | Listofcompaniesowned
----------

Now, I would like to write the RECORDSELECTIONFORMULA as part of the code,without using the editor.

I intend the user to choose the customer he wants to see, based on a customer number that he will provide.

Report.DataDefinition.RecordSelectionFormula = "{GENINFO.custno}=" &i
[i is a variable which takes userinput for customerno.]

Report.DataDefinition.RecordSelectionFormula = "{DETAILS.custno}=" & i

This doesnot work...
The first problem is how to couple these both...tables in the formula.

I used "And". ...doesn't work..

The second problem is that I want only custno field from GENINFO table to be part of my report.The custno field of DETAILS table is just meant for selecting the other fields in the table such as "listofcompniesowned" etc...
----------------
CrysReport1.rpt
----------------
custno | GENINFO.CUSTNO
mailid | DETAILS.MAILID
listofcompniesowned | DETAILS.LISTOFCOMPNIESOWNED
----------------
----------------

I tried dataset for this...by creating xsd file, but it gives me a "query engine error"......in a "c:/......./......./temp/......................rpt file".

I could not understand what it is this error all about ??

In general, please guide me as to the ways of using more than 1 table in a single report?//

Fast Replies welcome.

Thanks for the time.First of all, why are you trying to control the whole record selection formula from your VB.NET code? Why don't you just pass the value of "i" as a parameter to the CrystalReport and then your record selection formula would be something like this:

{GENINFO.custno}= <?i>

As far as linking the tables you should do that in your CrystalReport under Database>Visual Linking Expert. If you are only pulling data from 2 tables there really is no need to use datasets. DataSets make your report slower.

In many of my reports I pass all kinds of parameters like that and it works just fine.

Designing relational tables

Hi, not sure if this is the right forum for this question.

I am creating relational tables for the first time in sql server express. I will have an orderItems table and an orders table. the MenuItems table is the problem. It is a catalogue of books. There will be about ten columns. all are unique to each book. i.e isbn number, title, author, publisher etc. but ten columns seems to be quite cumbersome. it may be easier to break the table down into two tables (i.e. primary details and secondary details perhaps) However to populate the table in the first place it would be easier to have it as one table instead of opening and closing 2 tables Adding the odd book to the two tables in the future would not be a problem. so the question is can i create a table and then brak it into two relational tables afterwards. If so how do i do this. this is my foirst go at relational tables and i am still trying to get a handle on visualising them. If my logic is up the wall please let me know...

Nick

Yes you can do this. Once you have your "big" table populated simply create the second smaller table and just execute an update statement:

update mySmallTable
set field1 = b.field1
, field2 = b.field2
, field3 = b.field3
from myBigTable b

|||

Great. Im really moving forward now. thanks for your time

Nick

Designing Primary Key and Clustered Index and Performance

I have several tables where the clustered index (the
physical way the data is stored) is different from the
Primary Key (which is just a unique number). It seems to
me that this will help me the most, as the clustered
index supports my SELECT statements, and the Primary Key
column will support my UPDATE and DELETE statements. I
am very new to SQL Server. My question is this. Is
designing the tables this way ok to do, or is this not
how it should be done?
Second part of my question is, If what I am doing is
fine, then does it make a difference (performance wise)
if I have the Primary Key column the first column table
or about the seventh column in?
Thanks so much for your help.Depends on who you ask. Zealots will say that you should never, ever use an
arbitrary unique integer as a row identifier. I disagree. However,
whenever possible, even if you are using a unique identifier you should
attempt to set a 'natural' primary key based on uniqueness in your data, and
use this for your primary key rather than the row identifier. You can still
use the ID to make life simpler (e.g. passing back lists of rows to client
applications for singleton selection), but the primary key will help
maintain and validate the table's data.
As for location within the column list, it makes no difference where
anything is. Don't rely on the ordering of your column list. Always
specify explicit column lists in the order you want them for selects and
inserts.
"Nancy" <anonymous@.discussions.microsoft.com> wrote in message
news:052b01c3aee5$09ea4e00$a301280a@.phx.gbl...
> I have several tables where the clustered index (the
> physical way the data is stored) is different from the
> Primary Key (which is just a unique number). It seems to
> me that this will help me the most, as the clustered
> index supports my SELECT statements, and the Primary Key
> column will support my UPDATE and DELETE statements. I
> am very new to SQL Server. My question is this. Is
> designing the tables this way ok to do, or is this not
> how it should be done?
> Second part of my question is, If what I am doing is
> fine, then does it make a difference (performance wise)
> if I have the Primary Key column the first column table
> or about the seventh column in?
> Thanks so much for your help.|||Placing the clustered index on a column other than the primary key is often
wise.
You only get ONE clustered index per table so use it wisely (Which is what I
believe you are doing).
If you are new to SQL Server, welcome to the community.
I recommend starting with these 3 books:
1. Inside SQL Server 2000 by Kalen Delaney
2. Professional SQL Server 2000 Programming By Robert Vieira
3. Transact-SQL Programming by Kline
Cheers
Greg Jackson
Portland, OR