Calculating Rowsize For User Tables
Aug 6, 2001Hi,
how do i calculate rowsize for user tables in a database
Thanks in advance.....
jfk
ADVERTISEMENT
Calculating Avg Rowsize In My Db...
Nov 23, 2001Hi,
I'm using SQL Server 7.0 SP3.
I need to calculate the average size of a record in my database to assist in estimating growth.
Is there a quick and/or easy way of doing this?
Thanks in advance,
Darrin
Calculating Totals Based On User ID
Sep 14, 2013I have created an SQL report that returns order data from a shop DB.
How can you get the output to total the number of order instances and the total value of those orders, both per user_id? Is this possible?
Calculating Between Tables
Nov 15, 2005Hi All,
I have a very complicated project and for some reason I have decided to use SQL server as the backend database. I am new to it and have encountered my first problem. In basic terms what I want to be able to do is increment/decrement a field in a table according to something that happens in a different table. Where do I start?
So for example, a stereo system/cd player can have 10 cds at one particular time, when 2 cds are removed, then 8 slots are available. Then when 1 of the 2 remaining slots are filled then 1 slot remains and so on....so I want some sort of a counter field. Using that field I want to be able to generate a report on what is currently available.
Please Excuse my lame example but can anyone please help?
Thanks
Ben
Calculating Between 2 Tables In One Report
Jan 19, 2007Goodmorning,
In one report I have 2 tables. Both tables are related to a different data set. Now I want to create a calculating between a field of the table 1 and a field of table 2.
Does anyone know how to?
Thx anyway!
Calculating Data From Multiple Tables
Aug 29, 2014I have a table which contains readingtime (records every 4 seconds during the day) and an energy reading. I wanted to know how much energy was used in a day. This is the query that i used
SELECT date(readingtime), max(energy)-min(energy)as total FROM lights_1 WHERE readingtime between 20140407000000 and 20140409235959 Group by date(readingtime) LIMIT 0,30;
This gave me the desired results for 1 table. I have 4 tables of these lights_2, Lights_3, lights_4 and i would like to display them together and hence match up the reading time so each day has the energy requirements for all for tables with the total. When i try and join tables i get a lot of errors!
Calculating Values In Multiple Tables Within A UDF In SQL Server
Feb 24, 2005Hi,
I'm using scalar UDFs in SQL server to return computed values. I've been trying to use the same udfs to perform the same computations but from different tables, but I'm not sure how. Can someone please help???
Here's an example of a counter that I'm using to return the number of days.
CREATE FUNCTION [dbo].[MarketPulse_fn_Counter] (@date smalldatetime, @date2 smalldatetime, @osid int)
RETURNS int AS
BEGIN
return (select count(*) from MarketPulse_0ndqc
where stockosid = @osid and createdate <= @date and createdate >=@date2 )
END
How can I use this same UDF to do the same computation but to SELECT from another table and return that value?
How Do We Determine Which User Database Tables Are Mostly Retrieved By User Or Modified By User?
May 22, 2008
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
Rowcount, Rowsize
Sep 5, 2007Trying to get the rowcount, rowsize for every table in everydatabase?
I'm getting the same tables from the same db. anyone ideas how to fix it?
declare @db varchar(40)
declare @sql varchar(8000)
declare @cursor varchar(8000)
declare @table_name varchar(60)
declare a cursor for
select name from master..sysdatabases
open a
fetch a into @db
while @@fetch_status = 0
begin
print ''
set @sql = 'use [' + @db + ']'
print @sql
exec (@sql)
declare b cursor for
select table_name from information_schema.tables
where table_type = 'base table'
and table_schema = 'dbo'
and table_name not like 'dbo.%'
open b
fetch next from b into @table_name
while @@fetch_status = 0
begin
select @cursor = 'exec sp_spaceused ' + '[' + @table_name + ']'
print @cursor
-- exec (@cursor)
fetch next from b into @table_name
end
close b
deallocate b
fetch next from a into @db
end
close a
deallocate a
=============================
http://www.sqlserverstudy.com
Error On A Rowsize
Oct 9, 2007 I have a view that selects various fields, from various tbls.
When I run that view it erros out as follows:
Could not insert a row larger than the page size into a hash table. Resubmit the query with the ROBUST PLAN hint.
It seems like 1 particualr record, is exceeding the rowzise. and hence the errors.
How can I resolve this..
Regarding RowSize In SQL Server 2000
Feb 28, 2007Hi,I read this article on net recently.http://joseph.randomnetworks.com/archives/2005/08/30/sql-server-2000-maximum-row-size-8060-bytes/And according to it, SQL Server 2000 have around 8K of Row Size.Now consider this , i have table have two column and in each column i have inserted data around 6k.Now my question is , how sql server is going to store this.Is it in 2 different Row ?Can any one explains this or provides links related to it.ByeAmit
View 1 Replies View RelatedMysterious Rowsize Error
Nov 27, 2000Hi!
I get this mysterious error while running an update.
SQLServer tells me that my update row is to big to fit in a row.
Then I check the size of the row and and it is like 2000 bytes so
it should fit without any problem at all. The really strange about
it that when comments a field it works just fine. That field is always
null. Could that be the problem. Anyone who have ever had a similar
problem or so?
Find A RowSize On A Record
Dec 11, 2007I have a select statment that gives me an error, cannot go more than rowsize 8094.
I am trying to find the particualr record/acctno, where the rowsize is excedding the max limi of 8094 chars. Once I have the acctno, I will correct it.
But how do I find which acct has this issue? Bascailly how can I find which record size is more than 8094 chars?
Is Being Over The 8080 Rowsize Limit A Big Problem
Dec 19, 2001I have a SQL2000 database where the developers have a tool which creates screens and tables at the same time. During cleanup (dropping of some columns in these tables) I recieved the below error message
Warning: The table '%.*ls' has been created but its maximum row size (%d) exceeds the maximum numberof bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Apparently the tool is not sophisticated to recognize this problem. I looked at MS KB and it stated that inserts and updates may fail due to this. I probably know the answer to this, but has anyone else encountered this before, and what if any kind of problems did it cause. I am sure it is a no-brainer that this is asking for trouble, but if anybody has any experience with this issue all input appreciated.
How Do I Calculate The Current Maximum Rowsize
Jun 5, 2007I'm wondering how to calculate the current possible row width if all data where filled to max. I'm just curious if I'm approaching the 8060 max.
Sql Server 2005
Thanks,
Mitch
SQL 2012 :: How To Grant A New User Same Direct Privilege To Tables As Existing User
Feb 18, 2014I am relatively new to sql developer. There is a new user that just joined our organization. I am trying to grant him the same direct grants privilege to the tables that an existing user has. The existing user has a ton of direct table access privileges and it will take days if I had to do each grant one by one like: grant select,insert,delete,update on 'table name' to 'user id'. Is there a way of copying or inserting an existing user's privilege and granting it to a new user.
View 2 Replies View RelatedHow To Create Multiple Tables On The Fly So That Every User Each Has His/her Own Set Of Tables?
Aug 12, 2006Hello all,
Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.
Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.
What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1
Please help :-D
Greetingz,
DJ Roelfsema
List User Tables
Apr 14, 2001I'm brain-dead today, sadly. If it weren't for IE remembering previous entries, I don't know if my name and email would have made it into the header correct :-)
I want the SQL command that lists the names of all user tables.
Alternatively, I have the following problematic Access 2000 code:
Public Sub ListAllTables()
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim cnn As New ADODB.Connection
Dim i As Integer, j As Integer
Dim vgRet As Variant
Dim intPrefixLen As Integer
Dim strAppend As String
DoCmd.Hourglass True
cnn.Open CurrentProject.Connection
cat.ActiveConnection = CurrentProject.Connection
intPrefixLen = Len(CON_pkgPrefix)
Debug.Print cat.Tables.Count
For j = (cat.Tables.Count - 1) To 0 Step -1
Set tbl = cat.Tables(j)
With tbl
Debug.Print tbl.Name
vgRet = SysCmd(acSysCmdSetStatus, tbl.Name)
End With
Next
Set tbl = Nothing
Set cnn = Nothing
Set cat = Nothing
DoCmd.Hourglass False
vgRet = SysCmd(acSysCmdSetStatus, " ")
End Sub
This code runs fine against an MDB but against SQL it includes all the views, rather than just the tables. If you have a fix for this, that will do just fine!
Arthur
How To Copy All The Tables Into The Same Db With A Different User Name?
Nov 2, 2001I need to duplicate all the tables owned by dbo to another object ownername in a database within the same database.That means,if there are 100 table owned by dbo in a 'Database A'. I need to have a total of 200 tables in the same database 100 each for dbo and 100 for another user.They should have same schema including the keys etc and data.How is this possible?Through DTS we can't copy them to the same databse.We do not want to have a temp db inbetween to complete this process.Any ideas on how to do this? NOTE:This is not a one time job.
Thanks.
Sheila.
Look Up User Permisson Of All Tables
Mar 15, 2000There are more than 20 tables and 100s of users access the database.
what is the quickest way check what permission users have on the tables. Could anyone help me
User Tables Really Accesed By SQL
Jul 6, 2006I administre a DB that has about 2000 user tables, unfortunatly I havenīt documentation, and the people that create thouse tables are no more in the company.
How can I identify the user tables that really are used by SQL today?
I used the Profiler to identify the Stored Procedures that are called recently, but when I try to use Object:Opened event class to look the tables that are opened, it dosnīt display anything.
I hope you can help me.
Filter Tables According To User
Aug 29, 2006Hi,
Im new to SQL Server and Im having trouble to decide wich is the best way to filter the tables that one user can read and write.
For example the table Sales as a field named Branch (SMALLINT) with values 1 or 2 (the store that made the sale). User "A" can see and write only records where Branch = 1.
Im developing a windows program that shows all the purchased orders in a DataGridView. So should I make the filters (permissions) in the program or in the SQL Server?.
I hope I made my self clear (my english is not so good)
Query To Retreive User Tables In Sql Db
Jan 2, 2004Is there a way to create a query that will return all user tables inside a sql db
Thanx
JDBC API To Fetch Only User Tables
Apr 11, 2006Hi,
I am using JDBC DatabaseMetaData's getColumns() to get the table names, column names and datatype of the columns. As a result, I am getting user tables as well as the system tables. How to fetch only the user tables excluding the system tables like sysindexes, etc. I know this is possible by executing a select query (using SYSOBJECTS and xtype = 'U'). But, is there anyway to fetch only the user tables using the databasemetadata API.
Please advice,
MiraJ
Find The List Of Tables For A User?
Feb 18, 2015how to find the names of the tables owned by the particular user in sql server and how to display the distinct object types owned by the particular user.
View 1 Replies View RelatedSize Of User Tables In A Database
Nov 4, 2003The following procedure will display the size of all the user tables in a database.
CREATE proc sp_tablesize
as
if exists (select * from sysobjects where name = 'sp_tablesize')
begin
goto calculate_tablesize
end
else
begin
CREATE TABLE #SpaceUsed
(
TableName sysname,
TableRows int,
TableSize varchar(10),
DataSpaceUsed varchar(10),
IndexSpaceUsed varchar(10),
UnusedSpace varchar(10)
)
goto calculate_tablesize
end
calculate_tablesize:
declare @tablename nvarchar(50)
declare @cmd nvarchar(50)
declare c1 cursor for select name from sysobjects where xtype='u'
open c1
fetch c1 into @tablename
while @@fetch_status = 0
begin
set @cmd='exec sp_spaceused['+@tablename+']'
insert into #SpaceUsed exec sp_executesql @cmd
fetch next from c1 into @tablename
end
select * from #SpaceUsed
drop table #SpaceUsed
deallocate c1
Database Tables And User Names
Jan 26, 2007We have recently copied a database from one machine to another. On the old machine, when we access the tables we do not need to use the username.tablename convention to query them. On the new box we do.
For example, to query a table called Page we would nee to do this on the new box.
SELECT *
FROM webdev.page
unfortunately all the code is written, without the username prefix. Is there a way to not use the username prefix?
Thank you for your help...sorry for the newbie type question.
Can't Expand User Database Tables
Dec 5, 2007We have a person who CAN connect to a named instance in SQL Server Management Studio. There is nothing in the log for a failed login for him. However, when he tries to expand the Tables folder under the only user database in the instance, he gets the error: Failed to retrieve data for this request (Microsoft.SQLServer.SmoEnum). Additional Information: An exception occured while executing a Transact-SQL statement or batch. Select permission denied on object 'extended_properties',database 'mssqlsystemresource', schema 'sys'(Microsoft SQL Server, Error:229).
He is trying to expand the Tables folder under a database named ADSALLDB. He can see the folder as well as the other folders (i.e. Views, Synonyms, Programmability,etc.), but can't expand any of them. He can expand the folders under the system databases.
Other uses set up just like him can connect. He is set up with read/write access to the database ADSALLDB. He can expand the system databases.
This is a named instance in a 6 node cluster. SQL Server 2005 SP1 Build Level 2221.
Thank you for any help.
Prevent User From Deleting Tables
Dec 12, 2007Hi,
How can I prevent a colleage to delete tables in a specific database.
Yes he has access to Enterprise Manager. We would like to allow him read only to the live databases.
Is this possible?
Finding Out All The User Tables Being Used By An Application
Jul 23, 2005Here's the situation, developers inherit a web app from someone,backend SQL db has about 120 user tables and the db is also being usedby other apps. Developers don't have a list of user tables being usedby this app, now, I need to create a new db based on this one, whichwould be used by this app only. So, I intend to find all the usertables being used by this app, then copy its schema and possibly dataas well to a new db. FYI, current ERD is not this app.One option to find out all the user tables being used by this app is todo recursive search for FROM and JOIN against the full spectrum of theuncompiled source code, then, weed through such an extracted list whenin doubt about a particular table, one caveat is a portion of code withsql stmt could have already been commented (no longer being used thoughstill in the code).Better option?TIA.
View 3 Replies View RelatedAllowing A User Access To Only A Few Tables
Jul 20, 2005With MS SQL 2000 Enterprise Manager, is there a way to allow a user accessto only a few tables, but deny the user access to the rest without having togo to all of the tables and denying access? The database has roughly 50tables, but only 3 should be granted to the new user, so as you can see itwould be a painstaking task to manually do this with the *cough* mouse. Or,if I can run some sort of grant script, that would work too. Thank you!
View 2 Replies View RelatedQuery To Get All User Tables With Columns
Jul 20, 2005Hi,I tried to create a simple view as followsCREATE VIEW V_ALL_USERTABLE_COLUMNSAS(SELECTOBJ.NAME as TableName,COL.NAME as ColName,TYP.NAME AS TYPEFROMSYSOBJECTS OBJ,SYSCOLUMNS COL,SYSTYPES TYPWHEREOBJ.TYPE = 'U'AND OBJ.ID = COL.IDAND COL.TYPE = TYP.TYPE)Combined with consistent naming conventions I will use this view toeasily find foreign keys; a laSELECT *FROM V_ALL_USERTABLE_COLUMNSWHERE ColName LIKE ('%user_id')There is something wrong with my view definition that I don't getthough; it doesn't return all the columns. I have a table with thefollowing definitionCREATE TABLE [dbo].[c_messages]([cid] [int] IDENTITY (1, 1) NOT NULL ,[touser_id] [int] NULL ,[tosession_id] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fromuser_id] [int] NOT NULL ,[message] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[message_read] [bit] NOT NULL ,[logout] [bit] NULL) ON [PRIMARY]GOThe problem is that the select I used to define the view doesn'treturn the touser_id column. I have sort of a sneaking suspicion thatthe problem has to do with joining syscolumns.type to systypes.type,but I don't know what to do instead (I'd really like to include thetype; it's useful if I ever changed the type of a primary key and wantto check that I also changed all the foreign keys).Any help would be appreciated!
View 1 Replies View RelatedDropping Statistics From User Tables
Apr 21, 2008Hi,
I have founde such a nice code to create statements for dropping all statistics in a database.
DECLARE @tblname sysname, @statname sysname, @sql nvarchar(2000)
DECLARE c CURSOR FOR
SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
OPEN c
FETCH NEXT FROM c INTO @tblname, @statname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DROP STATISTICS [' + @tblname + '].[' + @statname + ']'
PRINT @sql
FETCH NEXT FROM c INTO @tblname, @statname
END
CLOSE c
DEALLOCATE c
Please help me with changing this code to take care only on indexes from my own tables (no system ones).
Thanks for your help.
Przemo