Is there a query I can run to retrieve a list of all tables and their sizes in a database? I want something that is like the feature in Enterprise Manager when you click on a database and then the 'Tables & Index' link. It lists the tables and their respective size. I want to push this into a spread sheet.
The reason why I am doing this is the compare data between 2 different databases. Since I cannot find a tool that will compare the data, the closest I can get (without bcp-ing out all data and comparing) is to look at the sizes of each table.
I ran the following query to get the log file sizes for all databases:
select (size*8.00)/1024,filename from master..sysaltfiles
When I compared the results from this query with the transaction log properties in EM, not all of the sizes match. For example, EM shows the transaction log size for tempdb to be 2 MB but the results of the query shows that transaction log size for tempdb is .5 MB. Which query can I run to get the numbers that would match between two? Thanks.
Hi, 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.
How can I get the table sizes for data and transaction logs just like we had in SQL Server 7.0 on the first screen of the Entreprise Manager?
I remember having a bar showing used space in blue and unused in magenta. I bet there are a couple of functions that can be added in a script that will retreive this info.
How can I get the table sizes for data and transaction logs just like we had in SQL Server 7.0 on the first screen of the Entreprise Manager?
I remember having a bar showing used space in blue and unused in magenta. I bet there are a couple of functions that can be added in a script that will retreive this info.
I have a table in my database and the table has almost 45 columns and the rowsize is 10468 bytes.in that most of the colums have varchar datatypes and and i think coz of poor knowledge of the data most of the columns with varchar data were given more column length. Now i want to decrease the size of those columns and to see the row size would be around 8k Bytes.If i do this now, does it affect the table performance much....Infact can i do this as there is lot of data (almost 2 million rows) in the table.If it is possible is there anything to be taken care before changing the column lenghts.
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
The 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 = '
I have a few databases that are using Partitioned Views in order to manage the table sizes and they all work well for our purposes. Recently I noticed a table that had grown to 400+ million rows and want to partition it as well, so I went about creating new base tables based on the initial table's structure, just adding a column to both table and primary key to be able to build a Partitioned View on them.The first time around, on a test system, everything worked flawlessly but when I put the same structure in place on the production system I get the dreaded "UNION ALL view 'DBName.dbo.RptReportData' is not updatable because the primary key of table '[DBName].[dbo].[RptReportData_201405]' is not included in the union result. [SQLSTATE 42000] (Error 4444)" error.
I have searched high and low and everything I see points to a few directives in order for a UNION ALL view to be updatable:
- Need a partitioning column that is part of the primary key - Need a CHECK constraint that make the base tables exclusive, i.e. data cannot belong to more than one table - Cannot have IDENTITY or calculated columns in the base tables - The INSERT statement needs to specify all columns with actual values, i.e. not DEFAULT
Well, according to me, my structure fulfills these conditions but the INSERT fails anyway. CREATE scripts below scripted from SQL Server. I only modified them to be on a single row - it is easier to verify that they are identical in a text editor that way.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO
i need to find how many Databases in my local SQL server (programatically).i know i have 8 databases is there but i need to print each and every database.
Is there a stored procedure to list attached databases? You can do this: use Master exec sp_helpfile go
Which shows you the config files for the current DB. What if you want to do a multiple sp_detach_db? If you could get a list of the attached DB's you could recursilvely or conditioinaly detach.
Is there a way I can query the database server (SQL 2000) to get a list of all the available databases? As a threshold problem I am not clear how I would connect to the server. I am using VB.Net and presently connect to one database at a time. The connection string is in an XML file, such as:
I would like to allow the user of my ADO.NET application to browse through a list of the available databases on a given server. INFORMATION_SCHEMA in SQL Server Books Online doesn't describe how to do this. How can I do this? TIA, Royce
Is there a way of limiting the "Databases list" to just the databases which I have permission to? My databases is in a hosted environment, and the list of databases on that server is HUGE.
Hi all,I have to write a program to read the names of databases in AnalysisServices. I don't know which table I can get this information from.Thanks a bunch
I have MSDE running on my system and I want to get a list of databasesfor that server programmatically. I'm using vb.net and I want to view,add, delete and modify databases on a server from within a class.I'm aware I can use the "Server Explorer" feature in Visual Studio, butthat isn't what I'm looking for. Any references or suggestions on whereto start are appreciated.TIARalf
Hey guys,Couldn't find this anywhere in google.I want a list of all database column names for a specific table/viewfrom across database.I tried this...-----------------------------------------------------Select *[color=blue]>From Information_Schema.Columns[/color]-----------------------------------------------------I also tried this...-----------------------------------------------------select syscolumns.name, sysobjects.name, * from syscolumns, sysobjectswheresysobjects.id = syscolumns.idand (sysobjects.xtype='U' or sysobjects.xtype='S')-----------------------------------------------------These queries return information about the CURRENT database.But, if I want to do it ACROSS database or across servers.. how can Ido this?I will express my gratitude to everyone who is kind enough to answerthis question. (I've been stuck with this problem for a while now.)Thanks!OhMyGaw!
Hi everyone, I have an instance with many databases in it. I am looking for the easiest way to see which of those databases a user has a login on. What is the most efficient way of doing this? Thanks, Anil
hi i have a project and i want make thats; 1-)The list All SQL Server(s) in my network to dropdrown list(that's ok) 2-) the list all databases to selected sqlserver(that's ok) 3-) the list tables to selected database(i cant)
i wrote my source kod please help me
1-) Dim mDMOApp As New SQLDMO.Application Dim mNames As SQLDMO.NameList Dim t As Integer
mDMOApp = New SQLDMO.Application mNames = mDMOApp.ListAvailableSQLServers() lstServers.Items.Clear()
For t = 1 To mNames.Count lstServers.Items.Add(mNames.Item(t)) Next
2-)
Dim server As New SQLDMO.SQLServer Dim db As SQLDMO.Database
server.Connect(mysqlserver, "xxx", "xxx") For Each db In server.Databases lst.Items.Add(db.Name)
Hi All, The web site I'm working on will allow admin users to create a new database for each new client. I'm hoping to be able to keep all of the databases in the local App_Data folder, but I'm finding that I can't even find a way of generating a list of the databases in the App_Data folder. The code I have is:Dim serverConn As New ServerConnection(".SQLEXPRESS") serverConn.LoginSecure = True serverConn.ConnectTimeout = 30 serverConn.Connect() Dim sqlServer As Server = New Server(serverConn)
For Each db As Database In sqlServer.Databases ListView1.Items.Add(db.Name) NextThis does populate a list of databases, but not the ones in the App_Data folder - just those attached directly to the server.Any help on this will be greatly appreciated as it's starting to drive me nuts!Thanks,Paul
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
Hi all, Now, I using Windows200o & SQL7.0. I want to permission a client connect to my server, and with his login, he only works with a specify datatbase. When he connects to my server, although he can't work other databases, but he can list of them in server - using EM (and I don't want he knows this). How can I solve this problem? Thanks.
I'm trying to list all database locations with SQL command, but getting lost here :(
So, anyone any idea how to get databasefile locations from server? All I need is like: 'C:Program FilesMicrosoft SQL ServerMSSQLDatamaster.mdf' and others.
