Thursday, March 29, 2012

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 table constraints for database upgrade/migration project

I have a project to update one of our program's database from one version to
another. Additionally, I will be migrating the data from one database to
another.
Here's an example:
mydb7.1
mydb8.0
Both databases exist in the same database server.
So mydb7.1 contains production data for an Online store. I need to migrate
this data in to the mydb8.0 database. The mydb8.0 database uses a newer
schema, so there will some work to convert the data to the newer layout.
Here's my problem mydb7.1 database contains hundeds of tables and foreign
key relationships. Is there some way or tool, that can help me determine
the table migration order? That way my code know's which tables to migrat
e
first, and still preserving the ref integrity.
Will it be easier to create the mydb8.0 database, just with tables, and then
migrate the data. After the data is migrated, then setup the foreign key
relations. That way, I will not need order the tables for migration? Jus
t
a thought.
ThanksRichard,
Just a thought...script out the table definitions...alter them...run and
create the tables in the new DB...transfer the data over...views or
dts...script out the PK and FK constraints then apply to the new tables in
the new DB.
HTH
Jerry
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:69ACA9B4-CB56-417B-8015-B3A168F82F36@.microsoft.com...
>I have a project to update one of our program's database from one version
>to
> another. Additionally, I will be migrating the data from one database to
> another.
> Here's an example:
> mydb7.1
> mydb8.0
> Both databases exist in the same database server.
> So mydb7.1 contains production data for an Online store. I need to
> migrate
> this data in to the mydb8.0 database. The mydb8.0 database uses a newer
> schema, so there will some work to convert the data to the newer layout.
> Here's my problem mydb7.1 database contains hundeds of tables and foreign
> key relationships. Is there some way or tool, that can help me determine
> the table migration order? That way my code know's which tables to
> migrate
> first, and still preserving the ref integrity.
> Will it be easier to create the mydb8.0 database, just with tables, and
> then
> migrate the data. After the data is migrated, then setup the foreign
> key
> relations. That way, I will not need order the tables for migration?
> Just
> a thought.
> Thanks

determine system database...

what is the best way to determine if a database is actually a system
database through tsql?
The only tangible thing that I can see is dbid. however I would far rather a
database property to check in t-sql.
Thanks in advance,
M.
cannot get it to work.
smo throws exception when I create the database object.
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:%23pkyNj%23OIHA.3816@.TK2MSFTNGP04.phx.gbl...
> Here is some idea.
> Check how Microsoft guys are doing that. Build a simple application that
> uses Microsoft.SqlServer.Management.Smo.Database.IsSyst emObject property
> for each database. Run Profiler and see how SMO is checking if the
> database is a system database or not.
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
>
> Uytkownik "Mark B" <nospam@.nospam.com> napisa w wiadomoci
> news:eEvpWO%23OIHA.4808@.TK2MSFTNGP05.phx.gbl...
>
|||cannot get smo property to work!
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:%23pkyNj%23OIHA.3816@.TK2MSFTNGP04.phx.gbl...
> Here is some idea.
> Check how Microsoft guys are doing that. Build a simple application that
> uses Microsoft.SqlServer.Management.Smo.Database.IsSyst emObject property
> for each database. Run Profiler and see how SMO is checking if the
> database is a system database or not.
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
>
> Uytkownik "Mark B" <nospam@.nospam.com> napisa w wiadomoci
> news:eEvpWO%23OIHA.4808@.TK2MSFTNGP05.phx.gbl...
>

determine system database...

what is the best way to determine if a database is actually a system
database through tsql?
The only tangible thing that I can see is dbid. however I would far rather a
database property to check in t-sql.
Thanks in advance,
M.Here is some idea.
Check how Microsoft guys are doing that. Build a simple application that
uses Microsoft.SqlServer.Management.Smo.Database.IsSystemObject property
for each database. Run Profiler and see how SMO is checking if the database
is a system database or not.
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uytkownik "Mark B" <nospam@.nospam.com> napisa w wiadomoci
news:eEvpWO%23OIHA.4808@.TK2MSFTNGP05.phx.gbl...
> what is the best way to determine if a database is actually a system
> database through tsql?
> The only tangible thing that I can see is dbid. however I would far rather
> a database property to check in t-sql.
> Thanks in advance,
> M.
>|||cannot get it to work.
smo throws exception when I create the database object.
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:%23pkyNj%23OIHA.3816@.TK2MSFTNGP04.phx.gbl...
> Here is some idea.
> Check how Microsoft guys are doing that. Build a simple application that
> uses Microsoft.SqlServer.Management.Smo.Database.IsSystemObject property
> for each database. Run Profiler and see how SMO is checking if the
> database is a system database or not.
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
>
> Uytkownik "Mark B" <nospam@.nospam.com> napisa w wiadomoci
> news:eEvpWO%23OIHA.4808@.TK2MSFTNGP05.phx.gbl...
>|||To the best of my knowledge, there is no such property available anywhere. I
believe that even SMO
and DMO goes by the database name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mark B" <nospam@.nospam.com> wrote in message news:eEvpWO%23OIHA.4808@.TK2MSFTNGP05.phx.gbl..
.
> what is the best way to determine if a database is actually a system datab
ase through tsql?
> The only tangible thing that I can see is dbid. however I would far rather
a database property to
> check in t-sql.
> Thanks in advance,
> M.
>|||Exactly Tibor. More precisely - by they go by name for master, msdb, tempdb
and model but there's more. Distribution database for replication can have a
custom name and is also considered to be a system database so they also
check for the value of is_distributor column in sys.databases for each
database.
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> napisal w wiadomosci
news:5900A85D-F0B1-4BAE-9FA4-BC2E37AB28CE@.microsoft.com...
> To the best of my knowledge, there is no such property available anywhere.
> I believe that even SMO and DMO goes by the database name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Mark B" <nospam@.nospam.com> wrote in message
> news:eEvpWO%23OIHA.4808@.TK2MSFTNGP05.phx.gbl...
>|||> so they also check for the value of is_distributor column in sys.databases
for each database.
Ahh, that's good to know. Thanks Pawel.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:%23oXeDGQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Exactly Tibor. More precisely - by they go by name for master, msdb, tempd
b and model but there's
> more. Distribution database for replication can have a custom name and is
also considered to be a
> system database so they also check for the value of is_distributor column
in sys.databases for
> each database.
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
>
> Uzytkownik "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.c
om> napisal w wiadomosci
> news:5900A85D-F0B1-4BAE-9FA4-BC2E37AB28CE@.microsoft.com...
>|||cannot get smo property to work!
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:%23pkyNj%23OIHA.3816@.TK2MSFTNGP04.phx.gbl...
> Here is some idea.
> Check how Microsoft guys are doing that. Build a simple application that
> uses Microsoft.SqlServer.Management.Smo.Database.IsSystemObject property
> for each database. Run Profiler and see how SMO is checking if the
> database is a system database or not.
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
>
> Uytkownik "Mark B" <nospam@.nospam.com> napisa w wiadomoci
> news:eEvpWO%23OIHA.4808@.TK2MSFTNGP05.phx.gbl...
>

determine system database...

what is the best way to determine if a database is actually a system
database through tsql?
The only tangible thing that I can see is dbid. however I would far rather a
database property to check in t-sql.
Thanks in advance,
M.Here is some idea.
Check how Microsoft guys are doing that. Build a simple application that
uses Microsoft.SqlServer.Management.Smo.Database.IsSystemObject property
for each database. Run Profiler and see how SMO is checking if the database
is a system database or not.
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
U¿ytkownik "Mark B" <nospam@.nospam.com> napisa³ w wiadomo¶ci
news:eEvpWO%23OIHA.4808@.TK2MSFTNGP05.phx.gbl...
> what is the best way to determine if a database is actually a system
> database through tsql?
> The only tangible thing that I can see is dbid. however I would far rather
> a database property to check in t-sql.
> Thanks in advance,
> M.
>|||cannot get it to work.
smo throws exception when I create the database object.
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:%23pkyNj%23OIHA.3816@.TK2MSFTNGP04.phx.gbl...
> Here is some idea.
> Check how Microsoft guys are doing that. Build a simple application that
> uses Microsoft.SqlServer.Management.Smo.Database.IsSystemObject property
> for each database. Run Profiler and see how SMO is checking if the
> database is a system database or not.
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
>
> U¿ytkownik "Mark B" <nospam@.nospam.com> napisa³ w wiadomo¶ci
> news:eEvpWO%23OIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> what is the best way to determine if a database is actually a system
>> database through tsql?
>> The only tangible thing that I can see is dbid. however I would far
>> rather a database property to check in t-sql.
>> Thanks in advance,
>> M.
>|||To the best of my knowledge, there is no such property available anywhere. I believe that even SMO
and DMO goes by the database name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mark B" <nospam@.nospam.com> wrote in message news:eEvpWO%23OIHA.4808@.TK2MSFTNGP05.phx.gbl...
> what is the best way to determine if a database is actually a system database through tsql?
> The only tangible thing that I can see is dbid. however I would far rather a database property to
> check in t-sql.
> Thanks in advance,
> M.
>|||Exactly Tibor. More precisely - by they go by name for master, msdb, tempdb
and model but there's more. Distribution database for replication can have a
custom name and is also considered to be a system database so they also
check for the value of is_distributor column in sys.databases for each
database.
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> napisal w wiadomosci
news:5900A85D-F0B1-4BAE-9FA4-BC2E37AB28CE@.microsoft.com...
> To the best of my knowledge, there is no such property available anywhere.
> I believe that even SMO and DMO goes by the database name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Mark B" <nospam@.nospam.com> wrote in message
> news:eEvpWO%23OIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> what is the best way to determine if a database is actually a system
>> database through tsql?
>> The only tangible thing that I can see is dbid. however I would far
>> rather a database property to check in t-sql.
>> Thanks in advance,
>> M.
>|||> so they also check for the value of is_distributor column in sys.databases for each database.
Ahh, that's good to know. Thanks Pawel.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:%23oXeDGQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Exactly Tibor. More precisely - by they go by name for master, msdb, tempdb and model but there's
> more. Distribution database for replication can have a custom name and is also considered to be a
> system database so they also check for the value of is_distributor column in sys.databases for
> each database.
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
>
> Uzytkownik "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> napisal w wiadomosci
> news:5900A85D-F0B1-4BAE-9FA4-BC2E37AB28CE@.microsoft.com...
>> To the best of my knowledge, there is no such property available anywhere. I believe that even
>> SMO and DMO goes by the database name.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Mark B" <nospam@.nospam.com> wrote in message news:eEvpWO%23OIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> what is the best way to determine if a database is actually a system database through tsql?
>> The only tangible thing that I can see is dbid. however I would far rather a database property
>> to check in t-sql.
>> Thanks in advance,
>> M.
>>
>|||cannot get smo property to work!
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:%23pkyNj%23OIHA.3816@.TK2MSFTNGP04.phx.gbl...
> Here is some idea.
> Check how Microsoft guys are doing that. Build a simple application that
> uses Microsoft.SqlServer.Management.Smo.Database.IsSystemObject property
> for each database. Run Profiler and see how SMO is checking if the
> database is a system database or not.
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
>
> U¿ytkownik "Mark B" <nospam@.nospam.com> napisa³ w wiadomo¶ci
> news:eEvpWO%23OIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> what is the best way to determine if a database is actually a system
>> database through tsql?
>> The only tangible thing that I can see is dbid. however I would far
>> rather a database property to check in t-sql.
>> Thanks in advance,
>> M.
>

Determine status of processing cube

From C# I need to determine if a cube is currently processing. We are trying to avoid running MDX against a cube while in the act - it results in an error message to our users.

Does anybody know of a way to do this? Maybe with AMO?

Thanks in advance,

John

Hello John,

There is no easy way of doing this in AMO, but you could use the DISCOVER_LOCKS schema rowset to test whether there are write locks on your cube. The LOCK_STATUS and LOCK_TYPE fields returned by this discover could help you find this out.

Hope this helps,

Artur