Create List Of Table Names And Size For A Database

May 5, 2004

Hi there,

I am trying to create a list of all the tables in one database and then list the size of each table. So for example I want to create a table with the table name and table size for one DB

E.g

Table1 1111KB
Table2 123300MB
Table3 120448KB

etc for all the tables in a particukar DB

I know there is a stored procedure to list the sizes: 'sp_spaceused' but not sure how to script all this together.

can anyone help please!!

From

NewToSQL

View 12 Replies


ADVERTISEMENT

How To Get The List Of Table Names In A SQL2005 Database

Aug 31, 2005

How to get the list of table names in a SQL2005 database?

View 6 Replies View Related

List Table Names In A Database Having A Particular Column.

Aug 3, 2007

Can anybody tell me how to find out whether a particular column exists in any of the tables of a database and if it does, display the table names?


Thanks

View 3 Replies View Related

SSIS Loading DWH Staging Area When Table Names Is Selected From Table List

Aug 31, 2007

Hello,
Maybe anyone have done that before?
I have table where i store SOURCE_TABLE_NAME and DESTINATION_TABLE_NAME, there is about 120+ tables.
i need make SSIS package which selects SOURCE_TABLE_NAME from source ole db, and loads it to DESTINATION_TABLE_NAME in destination ole db.

I made such SSIS package. set ole db source data access mode to table or view name variable.
set ole db destination data access mode to table or view name variable. set to variables defoult values (names of existing tables)
but when i loop table names is changed, it reports error, that can map columns, becouse in new tables is different columns.

how to solve that problem?

View 5 Replies View Related

List Column Names Of Sql Table

Mar 20, 2008

I know it is basics, just slipped out of my mind, How do we list or print the columns names of table in sql server 2000.
thanks,

View 10 Replies View Related

DB Design :: Getting The List Of Database Names From Remote Server?

Nov 2, 2015

In our application we are copying data between 2 sqlserver databases using linked server.  Say, sqlserver1 is source and sqlserver2 is destination, then, our application will be on sqlserver2 box and will copy data from sqlserver1 to sqlserver2.

User also need to choose from which database of sqlserver1, the data need to be copied. This data is our application data.. Nothing related to sqlserver database.

We are using the following query, to get the database names of the source sqlserver..

SELECT * FROM <linked server to dest db>.master.sys.databases

My questions is : is this query generic enough that works on all sqlserver versions?

Is the master database name of sqlserver configurable or its alwasys fixed as "master"?

View 4 Replies View Related

Syntax To Extract A List Of All Table Names On Linked Server

Aug 9, 2013

I'm using the following syntax to extract a list of all the table names on a linked server:

EXEC sp_tables_ex
@table_server = MY_SERVER_NAME

It outputs a list of tables into 4 columns in the result window.Is there a way an can use this as a 'SELECT * FROM ... " command so that I can organize records, insert into, etc etc ?

View 3 Replies View Related

List Table Name And Size

Jul 29, 2002

Anybody know any method/script I can use to see a list of table name
and table size (in bytes, not rows) within a database?
I know I can see the size of any particular table in "Table info" tab or using "sp_spaceused" procedure. But I can't generate a report with a list of table name and table size using these methods.
Any idea? Thanks.

John

View 2 Replies View Related

How To Create Table Names By Using Macro Variable? Thanks!

Oct 21, 2005

Greetings!I am now doing one type of analysis every month, and wanted to creattable names in a more efficient way.Here is what happens now, everytime I do the analysis, I will create atable called something like customer_20050930, and then update thetable by using several update steps. Then next month I will create atable called customer_20051031. Does anyone know if there is a betterway to do it? like using a macro variable for the month-end date? Noweverytime I have to change the table name in every single update step,which would cause errors if I forget to change one of them. By using amacro, I would only need to change it once.Thanks!

View 8 Replies View Related

To Create Unique Table Names And Access Them In Code...

Jan 17, 2006

Hi all,My program is a central data processing application built in ASP.We have different companies that use different web pages on another webapplication (from different countries) to load some inventory data(merchandise - clothes).Here is my requirement: Load different types of data (in differentformats) into a common set of tables, to do this I have to firstfilter, do lookup's, use cross-reference tables on this data and thenload it into a couple of tables.Since data is so different everytime, I want to have one main storedprocedure in which I can build the table (I know the format of thisdata so I know what columns/types to create) into which I will feed thedata.After this I will create other stored procs/udf's that reference thistable (probably from the same main stored proc) 'cleanse' the databefore loading into the actual tables.How feasible is my approach? (if you can call it one :-) I do not wantto have seperate tables for each country, that will be too many and sothis plan...If not any other ideas will be really helpful.thanks much

View 3 Replies View Related

How To Write A Script To Create A Table (With Unfixed Field Names)?

Mar 28, 2006

Hello All.

You may find below script stupid to you. I am trying to create a table with week number that is not fixed, i.e. March 2006 may have 4 weeks and April 5 weeks based on our company calendar.

And Field Names could be Grp, SubGrp, Week_10, Week_11, Week_12, Week_13 depending on the week being stored in TempWeekFile. We could start the month having only Week 10 and following week with Week 11 added into TempWeekFile.

My below script shows error. I have following records in my TempWeekFile

Week_No WeekCnt
10 1
11 2
12 3
13 4


How to write a script that do the above? Has anyone done this using a better method? Please advise. Thank you.

---------------------------------------------------------

if exists (select * from information_schema.tables where table_name='WeeklySalesToThird_Month')
drop table WeeklySalesToThird_Month

Declare @WeekCount int
Declare @Cnt int
Declare @WeekNo varchar(2)
Select @WeekCount = count(*) from TempWeekFile
Select @WeekCount
Select @Cnt=1

CREATE TABLE [dbo].[WeeklySalesToThird_Work_Month] (
[Grp] [varchar] (30) NULL ,
[SubGrp] [varchar] (30) NULL ,

while @Cnt<=@WeekCount
Begin
select @WeekNo = Week_No from TempWeekFile
where WeekCnt=@Cnt
[Week_@WeekNo] [Money] NULL
@Cnt=@Cnt+1
End

) ON [PRIMARY]
GO

--------------------------------------------------------

View 3 Replies View Related

Power Pivot :: Can Create A New Table That Shows Only Distinct Names

May 5, 2015

My DB contains company names repeating themselves several times (in the same column).

Can I create a new table that shows only the distinct names, and use it to work with the data?

My intention is to allow my users to choose only from the options within the DB (mimic in a way the list validation option in excel)

View 6 Replies View Related

DB Design :: Create Views From 2 Tables One Of Which Is Lookup Table That Will Give Column Names

Aug 5, 2015

I am trying a create views that would join 2 tables:

Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape

I want to create a view that looks like

ID, Color, Size, Flavor, Shape

View 4 Replies View Related

Query To Create A Countable List Using A Single Value From Table

Mar 15, 2004

Hi,

from a single numeric value, say 'n', in a field in a table, i'd like to create a SELECT query that produces a list from 1 to n, e.g

if the value was 6, I would like selected back:
1
2
3
4
5
6

Any pointers?
Greg

View 5 Replies View Related

Function To Create Comma Separated List From Any Given Column/table.

Jul 20, 2005

Hi,I'm sure this is a common problem.. to create a single field from awhole column, where each row would be separated by a comma.I can do this for a specified table, and column.. and I've created afunction using VBA to achieve a more dynamic (and very slow) solution..so I would like to implement it using a user defined function in sql server.The problems I'm facing are, that I can't use dynamic sql in afunction.. and I also can't use temporary tables which could build up a'standard' table from parameters given to then perform the function on.So, with these limitations, what other options do I have?Cheers,Chris

View 1 Replies View Related

Create A Table Of Contents Based On Report Items From A List Control

Apr 28, 2008

What are the options to create a table of contents based on the report items in a List Control? Document Mapping works for online viewing. A table of content would make the report easier to read when it's printed.

Any help is much appreciated. Thanks.

View 1 Replies View Related

Table Size And Database Size

Mar 2, 2008

Hi,
i use this script that show me the size of each table and do the sum of all the table size.

SELECT
X.[name],
REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],
REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],
REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],
REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],
REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]
FROM
(SELECT
CAST(object_name(id) AS varchar(50)) AS [name],
SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows],
SUM(CONVERT(bigint, reserved)) * 8 AS reserved,
SUM(CONVERT(bigint, dpages)) * 8 AS data,
SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size,
SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused
FROM sysindexes WITH (NOLOCK)
WHERE sysindexes.indid IN (0, 1, 255)
AND sysindexes.id > 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY sysindexes.id WITH ROLLUP) AS X
ORDER BY X.[name]

the problem is that the sum of all tables is not the same size when i make a full database backup.
example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when
i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?

THX

View 5 Replies View Related

How To Get All Table Names From A Database?

Apr 13, 2007

I have two questions:



1. Which system table (data dictionary) I can select to get all table names in one database?

2. What sql command can I use to get a definition about a table?



Thanks

View 9 Replies View Related

Getting Table Names From AdventureWorks Database

Apr 24, 2008

Hello,
I have ran into a problem when i'm getting table names in c#.
I have tried the sql command:
select name from sysobjects where type='U' order by name
So it returns the names without a problem. But when I try to access to some tables using their names (to retrieve the columns) I sometimes get the error "Invalid object name" following by the name of the table.
So I realized the if the table was not under dbo user then i would get this error.
for example: "select name from ContactCreditCard"
 gives me error because in database management tool it is shown like this: Sales.ContactCreditCard
How can i fix this problem? How to get that prefix of the tables?
 
Thank you,
 

View 8 Replies View Related

How Can I Find All Table Names Defined In A Database?

Mar 10, 2001

I have a database and I want to retrieve informations about all tables
defined in this database. In other words I need the table list defined in
database. Can you help me?

Thank you.

View 1 Replies View Related

SQL 2005 Database Design (Table Names)

Apr 14, 2006

In the past, we had table names such as:
PurchaseOrders
SalesOrders

Now with SQL 2005, you can use schema namespaces, so it could become:
Purchasing.Orders
Sales.Orders

So essentially you can use exactly the SAME table names, in different schemas.

So... would you recommend to change to the last model, or just stay with the old way?
What is best practice?



One of the reasons why I ask this question is that I looked at the AdventureWorks sample database included with SQL Server 2005, and Microsoft is using:

Sales.SalesOrderDetail
Purchasing.PurchaseOrderDetail
etc...

Why not:
Sales.OrderDetail
Purchasing.OrderDetail
etc...

????????

View 1 Replies View Related

Boolean: {[If [table With This Name] Already Exists In [this Sql Database] Then [ Don't Create Another One] Else [create It And Populate It With These Values]}

May 20, 2008

the subject pretty much says it all, I want to be able to do the following in in VB.net code):
 
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
 
How would I do this?

View 3 Replies View Related

SQL To List Column Names

Nov 26, 2002

Can anyone help me with a SQL statement that will list all the column names in a table please ?

I just want to list out the column name so that i can develop asp/vb more effectively than having to use SQLEM and Design table to see the field names.:confused:

thanks
FatherJack

View 2 Replies View Related

Dynamic Create Table, Create Index Based Upon A Given Database

Jul 20, 2005

Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.

View 1 Replies View Related

Giving A Proc Database And Table Names As Parameters

Jan 10, 2007

Hello!Is it possible to pass a stored procedure a parameter, say @table anduse it as a table in the sql command?Finally i want a proc to copy tables from a database to another database.THERE IS MY CODE:CREATE PROCEDURE [user].[copytable]@dbSRC varchar(100),@dbTRGT varchar(100),@table varchar(100)ASBEGIN TRANSACTION FreeAndCopyTableTRUNCATE TABLE [@dbTRGT].admin.[@table]INSERT INTO [@dbTRGT].admin.[@table]SELECT *FROM [@dbSRC].admin.[@table]COMMIT TRANSACTION FreeAndCopyTableIF @@error <0 GOTO E_Copy_FailE_Copy_Fail:ROLLBACK TRANSACTION FreeAndCopyTableGOthanks in advance,muh

View 2 Replies View Related

Dynamically Pass Table Names And File Names To IS Package

Mar 1, 2015

I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.

The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.

View 1 Replies View Related

Can CREATE DATABASE Or CREATE TABLE Be Wrapped In Transactions?

Jul 20, 2005

I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND

View 2 Replies View Related

Determine Table Names And Column Names At Runtime?

Jan 22, 2004

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.

View 5 Replies View Related

Database Table List

Sep 10, 2007

Is there a system table that holds the table definitions, names, properties, etc. for tables that I have designed? I would like to write a transact SQL query to insert a field in every table that I have defined.

View 1 Replies View Related

List Of Table In A Database

Apr 1, 2006

Please how can I get list of tables in a database? Thanks.

View 3 Replies View Related

How To Get The List Of Names Of The Databases Of A Server Using OleDbConnection In C#.......?

Mar 13, 2008

I want to get the names of the databases of the server using OleDbConnection in c# which i have to add in to a combobox.....the same task i achieved using SqlConnection where in there is a Class SqlClientCollectionNames...the code is as followsusing (SqlConnection connection =new SqlConnection("Data Source=server;User ID=username;Password=passwd")) {                connection.Open();                DataTable myData = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases);                foreach (DataRow row in myData.Rows)                   comboBox1.Items.Add(row[0]);               connection.Close();}   but the same i did for OleDbConnection it is not working using (OleDbConnection connection =new OleDbConnection("Provider=SQLOLEDB;Data Source=server;User ID=username;Password=passwd"))
 {
                connection.Open();
                DataTable myData = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases);//no such method Databases  present in OleDb namespace
                               foreach (DataRow row in myData.Rows)
                   comboBox1.Items.Add(row[0]);
               connection.Close();
}    is there any other way to do this using OleDbConnection........??Please help 

View 2 Replies View Related

List The System Names Has Sql Server Installed

Jun 30, 2005

hi,I need to display the system names which has sql Server installed. How it can be done in vb.net. Help me plzregardsSomu

View 1 Replies View Related

List Of All Relations Of Database's Table.

Dec 16, 2006

Hi,How to get list of all relations of certein database's table?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved