Query To Give All Table Sizes On A Database (was Query Help)
Mar 9, 2006Hi,
Does anyone has query to give all table sizes on a database?
Appreciate your help.
Thanks
ADVERTISEMENT
Query Table Sizes
May 17, 2002Hi,
I am looking to runa query to get the sizes of the tables in my SQL 7 DB.
I know I can access the info in Enterprise Manager, under "Tables & Indexes".
But I need to get this info via a query.
I need rows and size.
I figured out how to get rows through the sys tables:
select sysobjects.name, sysindexes.rows
from sysobjects,sysindexes
where sysobjects.name = sysindexes.name
and xtype = 'U'
Is the size of each table stored in a sys table as well? I can't find it.
Query DB For Table Sizes/number Of Rows
Dec 10, 2007Hi
I have a database with 150 tables. I want to make a query ranking those tables by table size and/or number of rows.
Is this possible?
Many thanks
ds9
Can Anyone Give Me A Query For This ?
Jul 20, 2005These are my table structures . Could you please suggest me analternative:tblArticles: ArticleID,Title,Summary,ContextSubTypeID,PostDatetblArticlePages: ArticlePageID,ArticleID,PageNum,ContenttblAuthors:AuthorID, NametblArticleAuthors:ArticleID,AuthorIDtblContextSubtype:ContextSubTypeID,NameI need to get Author,PostDate,Title,tblContextSubType.Name,PageN um,Content,- these have to grouped accordingly by the respective fields. Contentneed not be grouped by.
View 2 Replies View RelatedGive Sql Query For This Illustrations
Mar 28, 2008Hai friends
i have table student with 2 fields s_id,s_name
s_id s_name1 raja2 ramu3 vinoth4 muthu5 arun
i have another table test with s_id who wrote test...
s_id 25
now i have to display like below....give me sql query
s_id s_name2 ramu5 arun
Thanks in Advance...pl Help me......
Give Me Sql Query For One Illustrations
Mar 28, 2008Hai friends i have table
s_team
id name game
1 raja cricket
2 ravi football
3 muthu cricket
4 pravin cricket
5 hurry football
I have to diplay the above table in the following ...pl give me sql query
game totalplayers
cricket 3
football 2
T-SQL (SS2K8) :: Capture Database Sizes Table With Date
Jun 5, 2014I'm trying to capture the sizes of all Databases into a Permanent Table and include the Date.
It works when inserting into a #Temp Table.
When I try inserting into a permanent table it returns NULL.
The following code needs modified to create a permanent table and store the Date:
CREATE TABLE #databases ( DATABASE_NAME VARCHAR(50), DATABASE_SIZE FLOAT, Date VARCHAR(100) )
INSERT #Databases EXEC ('EXEC sp_databases');
SELECT@@SERVERNAME AS SERVER_NAME, DATABASE_NAME,
DATABASE_SIZE AS 'KB',
ROUND(DATABASE_SIZE / 1024, 2) AS 'MB',
ROUND((DATABASE_SIZE / 1024) / 1024, 2) AS 'GB',
CONVERT(date, getdate()) AS Date FROM #databases
DROP TABLE #databases;
Transact SQL :: Report Table Sizes In Each Database On Server
Aug 4, 2015The below query works perfectly fine, except that it  produces many outputs instead of one continuous table that can be easaly converted to xml / csv by copying it from the "Results" window.Â
What I need is a query that will produce a single result for all the tables in all the databases on the server.
The results:

The query:
DECLARE @begin INT = 1, @end INT, @sql NVARCHAR(MAX)
DECLARE @CREATE_TEMPLATE VARCHAR(MAX);
DECLARE @DBNAME VARCHAR(255);
DECLARE @SQL_SCRIPT VARCHAR(MAX);
SELECT @end = COUNT(name) FROM sys.databases
SET @CREATE_TEMPLATE = '
[Code] ....
DateRange Query Give Wrong Results
Sep 1, 2004I am running some query with the following where clause
where DateCreated BETWEEN '06/01/2004' AND '06/30/2004'
It gives the records where DateCreated is of 2004-07-01 21:48:02.377
the default language on the server is British English.
Could anybody please tell me the reason why records from july are also coming
Please Give Me Your Suggestions On Report Query - Very Urgent
Oct 25, 2006I need to disaplay number of Active Agencies on monthwise in one of my report. I have tbl_Agency table with ActiveDate and ActiveFlag. ActiveDate column contains always first Activation Date. If any chances in the agencies(update/delete) the same record will move to tbl_AgencyHistory table.
"If an agency is inactivated in September 10th, inactivated all of October, and then reactivated November 10th - the agency would be counted in September, not in October and counted in November"
ActiveDate column has always first activation date, I could not meet this requirement. This is very urgent issue, Could you please help me on this.
Thanks,
Malar
OPENROWSET Query Will Give Syntax Error - Please Help Me
Feb 28, 2008I am not able to use WHERE Clause in my query. What am I doing wrong?
Here my query that will generate error:
SELECT * INTO LN_S
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM LN_ACCT WHERE trn_dt > '2007-03-08' '
I am getting this error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '2007'.
Here is my query which doesn't generate error:
SELECT * INTO LN_S
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM LN_ACCT'
Using SQL Server 2000
DSN to a CACHE database on local network
Thanks in advance,
Sam
Using SUM To Give Interim Totals Every 'x' Rows From A Query
Jun 29, 2007First off, here is the application.
An airport baggage handling system distributes bags using multiple conveyors.
Bag counts are logged every 15 minutes. There is a count for each conveyor.
Example Log Table layout is as follows (The TIME column is DateTime, the Convx columns are TinyInt)
Time Conv1 Conv2 Conv3 Conv4 Conv5 Conv6
i 3 2 3 4 2 1
i+15min 2 3 4 2 2 2
i+30min etc.....................
i+45min
i+60min
etc...
The management team wants a throughput report which will take the following parameters in order to filter the results:
Begin Date
End Date
Time Interval (selectable as 15mins, 30 mins, 45mins, 60 mins and Daily)
My question is this. Given that my raw data has 1 row for every 15 minutes, if they select 60 minutes as their interval I need to run the query with the start and end dates but Sum every 4 rows and display it as 1 row, likewise if they select 30 minute interval, I need to sum every 2 rows. How do I run a query and SUM the Conv count data for every x number of rows and use the 1st TIME value in the returned x row summary?
Thanks for your help and let me know if I need to clarify anything
Create A Query That Will Give Result Set Containing Primary Order On Type
May 14, 2012I have a table with plant types and plant names. Certain plants are grouped on a custom field, currently called Field. I am trying to create a query that will give me a result set containing the primary order on Type, but need items with the same 'Field' value grouped by each other.For example, the following shows a standard query result with "order by Type", ie select * from plants order by Type
Code:
ID Type Name Field
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
5 Type5Name5(group2) -group2
6 Type6Name6(group6)
But I want it to look like this, with fields of the same value located next to each other in the result set (but still initially ordered by Type)
Code:
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
5 Type5Name5(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
6 Type6Name6(group6)
Transact SQL :: Create A Pivot Query That Will Give Result That Is Horizontal
Jul 8, 2015I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?
View 10 Replies View RelatedTransact SQL :: Query To Give Results Of All Configured Alerts Occurring History
May 14, 2015We created sql alerts on all our sql servers environments. Now, i want to see each sql server which sql alerts so far got fired and which one never occurs. is there any way, we can get this information from any system database?
View 9 Replies View RelatedPlease Help Me To Optimize This Sql Query, It Takes 28 Seconds To Return Result. Please Give Me A Tips Where I Went Wrong?
Aug 21, 2006SELECT * FROM
( SELECT TOP 15 * FROM
(SELECT TOP 15 CMDS.STOCKCODE AS CODE,CMDS.STOCKNAME AS NAME,CMDS.Sector AS SEC, CMD7.REFERENCE AS REF,T1.HIGHP AS HIGH,
T1.LOW,T1.B1_CUM AS 'B/QTY', T1.B1_PRICE AS BUY,T1.S1_PRICE AS SELL,
T1.S1_CUM AS 'S/QTY', T1.D_PRICE AS LAST,T1.L_CUM AS LVOL,T1.Chg AS CHG,T1.Chgp AS CHGP, T1.D_CUM AS VOLUME,substring(T1.ST,7,6) AS TIME,
CMDS.SERIAL as SERIAL FROM CMD7,CMDS,CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 ,CMDS WHERE
T1.SERIAL=T2.SERIAL
AND CMDS.SERIAL=T2.SERIAL
AND T2.sd='20060821'
AND CMDS.sd='20060821'
AND T2.L_CUM < '1900'
AND CMDS.sector >='1'
AND CMDS.sector <='47')
AND CMDS.SERIAL=T1.SERIAL AND
CMDS.SERIAL=CMD7.SERIAL AND
CMDS.sd='20060821' AND
CMD7.sd='20060821' AND
T1.sd='20060821' AND
T1.L_CUM < '1900' AND
CMDS.sector >='1' AND
CMDS.sector <='47' ORDER BY T1.D_CUM desc)
AS TBL1 ORDER BY VOLUME asc) AS TBL1 ORDER BY VOLUME desc;
Does It Store All The Results To Tempdb Database When I Query Against A Large Table Which Joins Another Table?
Jun 25, 2007Hi, all experts here,
I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.
With best regards,
Yours sincerely,
How To Query Database Table
Oct 6, 2005hi, i've got problem on how to query my two tables:Table statuslogFIELDS: row1 row2ActId : 1 2ActDate : 2005-9-19 2005-9-18PIN(employee) : P120 P120ProjCode : 1234 123ActCode : B IMap# : map1 kd145RegHrs : 0.5 7.0OtHrs : 0 2.0Status(%) : 20 100
Table DalsDataNewFIELDS: row1 row2ID : 24 25Date : 2005-9-19 2005-9-18PIN(employee) : P120 P120ProjCode : 1234 123ActCode : B IActMedium : W(PC) W(PC)Map# : map1 kd145RegHrs : 0.5 7.0OtHrs : 0 2.0Flag : 0 1Approved by : P084 P083
if you will notice some fields of my tables have same value. these are: Date,ProjectCode,ActCode,RegHrs,OtHours
what i would like my output(data) to be in my datagrid:ActId,ID,Date,ProjectCode,ActCode,ActMedium,RegHrs,OTHrs,Status,Flag,Approved by
i have this query in 1 table only."SELECT * FROM statuslog WHERE statuslog.Pin = '"+Session("user")+"' and statuslog.ActDate >= '"+DateFirst+"' and statuslog.ActDate <= '"+DateEnd+"' ORDER BY statuslog.ActDate DESC"
but what i would do now is to query the two tables and same where clause in the query above.
Database Table - Hierarchical Query
Aug 29, 2014Following is my db table
student_id student_code student_parent_id student_name
1 11 0 a
2 111 1 b
3 1111 2 c
4 11111 3 d
How to generate following op?
student_id student_code student_parent_id student_name Hierarchy
1 11 0 a 11 - 111
2 111 1 b 11-111-1111
3 1111 2 c 11-111-1111-11111
4 11111 3 d 11111
Want To Query One Specific Record From Database Table
Feb 27, 2008I have two tables in my database: order_id with fields order (text) and comp_ID (int) and another table called customers with comp_ID (int) and company name (text) and other company information fields. The link between the two tables is the comp_ID. With every order that's made the company that made the order is stored with it in the order_id table. If I type in the order id (text), I want to be able to use the order id to search the order_id table and find out what the comp_ID of the company that made that order is. Then use that comp_ID to pull up the record of company information from the customers table with the same comp_ID. Is there some way to do this in one query? Or how do I accomplish this?
View 4 Replies View RelatedSQL 2012 :: Query Did Not Run / Or Database Table Could Not Be Opened
Dec 15, 2014I have a view saved on server - mhsvi-datawarehousedatawarehouse.This view, in it's TSQL connects to a databasethat is set up as a linked server. That server is mhsvi-sql2008ainstance1.When I try to add the view to Excel in order to automatically refresh for users as a report - I get the following error - (I get it as well)
The query did not run, or the database table could not be opened.Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again.I have access to the database where the view is saved and the database that the TSQL calls.
SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table
Jun 26, 2015how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
Writing Query To Dynamically Select A Database Table
Sep 24, 2006Is there a way I can write a query to dynamically select a database table. That is, instead of having a variable for a certain column like customerId which would be €ścustomerID = @customerID€? I want to use the variable to select the table. I assume I may have to use a stored procedure but I am unclear as to how this would be coded.
Thanks
Transact SQL :: Query To Retrieve To What Database A Table Belongs
Jul 16, 2015I need a query to list the tables in SQL sever with respective database they belong across databases.
I can obtain list of tables from Sys.Tables / Sys.Objects Views but how do I correlate to which DB a table belongs to?
The Sys.Databases View list the  databases with their respective Ids. But, the DBID is not part of Sys.Tables / Sys.Objects Views.
Which View will allow me to fetch database to table mappings?Â
Stored Procedure Dbo.SalesByCategory Of Northwind Database: Enter The Query String - Query Attempt Failed. How To Do It Right?
Mar 25, 2008Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SalesByCategory]
@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'
BEGIN
SELECT @OrdYear = '1998'
END
SELECT ProductName,
TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Public Class ConnectionPoolingForm
Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'Force app to be available for SqlClient perf counting
Using cn As New SqlConnection()
End Using
InitializeMinSize()
InitializePerfCounters()
End Sub
Sub InitializeMinSize()
Me.MinimumSize = Me.Size
End Sub
Dim _SelectedConnection As DbConnection = Nothing
Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub DisableAllButtons()
btnAdd.Enabled = False
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
btnClearAllPools.Enabled = False
End Sub
Sub EnableOrDisableButtons(ByVal cn As DbConnection)
btnAdd.Enabled = True
If cn Is Nothing Then
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
Else
Dim connectionState As ConnectionState = cn.State
btnOpen.Enabled = (connectionState = connectionState.Closed)
btnQuery.Enabled = (connectionState = connectionState.Open)
btnClose.Enabled = btnQuery.Enabled
btnRemove.Enabled = True
If Not (TryCast(cn, SqlConnection) Is Nothing) Then
btnClearPool.Enabled = True
End If
End If
btnClearAllPools.Enabled = True
End Sub
Sub StartWaitUI()
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
End Sub
Sub EndWaitUI()
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub SetStatus(ByVal NewStatus As String)
RefreshPerfCounters()
Me.statusStrip.Items(0).Text = NewStatus
End Sub
Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click
Dim strConn As String = txtConnectionString.Text
Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()
Try
bldr.ConnectionString = strConn
Catch ex As Exception
MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)
Return
End Try
Dim dlg As New ConnectionStringBuilderDialog()
If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then
txtConnectionString.Text = dlg.ConnectionString
SetStatus("Ready")
Else
SetStatus("Operation cancelled")
End If
End Sub
Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
Dim blnError As Boolean = False
Dim strErrorMessage As String = ""
Dim strErrorCaption As String = "Connection attempt failed"
StartWaitUI()
Try
Dim cn As DbConnection = _ProviderFactory.CreateConnection()
cn.ConnectionString = txtConnectionString.Text
cn.Open()
lstConnections.SelectedIndex = lstConnections.Items.Add(cn)
Catch ex As Exception
blnError = True
strErrorMessage = ex.Message
End Try
EndWaitUI()
If blnError Then
SetStatus(strErrorCaption)
MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
SetStatus("Connection opened succesfully")
End If
End Sub
Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click
StartWaitUI()
Try
_SelectedConnection.Open()
EnableOrDisableButtons(_SelectedConnection)
SetStatus("Connection opened succesfully")
EndWaitUI()
Catch ex As Exception
EndWaitUI()
Dim strErrorCaption As String = "Connection attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click
Dim queryDialog As New QueryDialog()
If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
Try
Dim cmd As DbCommand = _SelectedConnection.CreateCommand()
cmd.CommandText = queryDialog.txtQuery.Text
Using rdr As DbDataReader = cmd.ExecuteReader()
If rdr.HasRows Then
Dim resultsForm As New QueryResultsForm()
resultsForm.ShowResults(cmd.CommandText, rdr)
SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))
Else
SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))
End If
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Using
Catch ex As Exception
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
Dim strErrorCaption As String = "Query attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Else
SetStatus("Operation cancelled")
End If
End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
Thanks in advance,
Scott Chang
How To Create A Make-Table Query With A Union Query
Oct 30, 2006I have successfully execute a union query. How can i create a make-table query to accomodate the resultset of the union query?
View 2 Replies View RelatedNaming Query Column Name Of Resulting Table Name From Query
Oct 23, 2014I need to name make the name of a column the same as the name of a table from the result of a sql query.. here is the assignment question below..I can't figure out how to get the name of the table to be inputed as the column name..
Write a script that uses dynamic SQL to return a single column that represents the number of rows in the first table in the current database. The script should automatically choose the table that appears first alphabetically, and it should exclude tables named dtproperties and sysdiagrams. [highlight=#ffff11]Name the column CountOfTable, where Table is the chosen table name.[/highlight]
Hint: Use the sys.tables catalog view.
I can figure out the rest. and actually have alredy done it, but i cannot figure out how to do the part that is highlighted above. I looked at lots of things on google to figure it out but no luck..Can some just give me some directlon or an example..
Transact SQL :: Query Plan Shows Table Not Even In Query?
Jul 22, 2015I am trying to optimize a stored procedure in SQL 2008. Â When I look at an actual execution plan generated from when I run it in SSMS it shows a table being used in the plan that has no relation to what is actually in the query script and this is where the biggest performance hit occurs.
I've never seen a table show up before that wasn't part of the query. why this might occur and how to correct it? Â I can't just change the query script because the table in question isn't there.
How We Run “SELECT� Statement (in SQL SERVER Query Analyzer) For A Table In Different Database.
Sep 11, 2006Hi All, Please let me know how we can need to run “SELECT” statement (in SQL SERVER query analyzer) for a table in different database without going/loading DATABASE B (i.e. without running “USE B”) e.g. we are in a database “A” and we need to run “SELECT” command for a table in Database say “B” without going/loading (i.e. without running “USE B”) the Database “B” Thanks in Advance J
View 3 Replies View RelatedSQL 2012 :: Query Based On Column Name / Setting Up Database Table
Oct 30, 2015I have a set of data spread across a number of tables regarding stock market data. An example of this follows:
Market Capitalization...
Date CompA CompB
01/01/11 100 5
02/01/11 102 4
Share Price....
Date CompA CompB
01/01/11 100 100
02/01/11 101 99
Event Data...
Date Company
01/01/11 CompA
02/01/11 CompB
Pretty simply, I need a way to retrieve the market capitalisation and share price data based on the event data. So for instance I say 'oh, there is an event on the 01/01/11 involving company A, the market capitalisation on this day was 100, then for the next event it was 4 for company B.
I can also transpose the data so that the company name is in the rows and the dates in the columns for the market cap and share price tables, but this leads to the issue that when I try and get the data, I don't know how to query the correct company for that date.
For instance:
SELECT Event.Date, Event.Company
FROM Event
how do I now say.....
SELECT MarketCapitalisation.Column
WHERE Column = Event.Company
AND MarketCapitalisation.Date = Event.Date.
I have played around with a few basic joins, but I am having issue with the principle of that second to last line of SQL (so only getting the correct column).
I still have a copy of the data in excel so can flip things around as needed, but that would only mean that I would have the issue of WHERE Column = Event.Date instead of Event.Company.
Programatically Query Database And Save Report In An Excel Table
May 2, 2007Hello All,
I am working on constructing a software layer around some features of the RDL language that would allow me to programatically generate reports.
I am reading the RDL specification language, and I do not understand three things:
1) How the DataSet element is populated by the query or more precisely how do the <Field> elements capture all the rows inside the table that is being queries?
To my understanding I wilkl have to define several fields that correspond to all columns of interest in the query.
But that is only for one row (?!) How do the rest of the rows get populated? Does the server recursively create new rows based on my definitions until it matches row for row all the data in the table?
2) Once the elements are inside a DataSet how do make use of that data to render it in a table.
I understand how the DataSource, DataSet, and Table work individually, yet I do not understand how to establish a flow of data between DataSet and Table.
3) Do I even need to use a <Table> as an RDL element in order to organize the data in an excel table?
I would appreciate any help. Thank you!
Transact SQL :: Query To Check Properties On A Table When Creating Database?
May 20, 2015I'm wondering if there is some sql I can run to check properties on a table. This would be used to verify things like data types, allow nulls and default values have been set to avoid mistakes. This could be done manually one table and one column at a time, but it would be a lot easier to look at it in the results window.
View 5 Replies View RelatedReducing Database And Log Sizes
Dec 16, 1999I have inherited a number of databases which were substantially over sized when they were set up. I'd like to reduce both the log and database files to be smaller than their original sizes, what's the easiest way to do this? If anyone has any experience of doing this please reply.
View 1 Replies View Related