Database Properties Query
May 17, 2007
I've been asked to provide some information about databases on an sql server.
I can use the following query to provide the names of all the databases:
select * from master.dbo.sysdatabases
What I'm also looking to provide is the current "size" of the database. I'm trying to find a table that I can link to that would provide the database size. I can manually find this number by right clicking on the database from enterprise manager, selecting "properties" and picking it up from the general tab.
This is sql server 2K...
Thanks.
View 2 Replies
ADVERTISEMENT
Oct 2, 2006
Hi,I'm creating a User Interface to display Sql database Properties, but I cannot find the right query to retrieved the info on database properties. The status that I get is "ONLINE", but it should be "NORMAL". Cannot find the right query to get the date of last database backup, last transaction log backup, and maintenance plan, etc.. Please help me [:'(]
View 2 Replies
View Related
May 20, 2015
I'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 Related
Dec 3, 1999
What are the differences between the database permissions, which can be granted in the database properties permissions tab (create table, create procedure etc.) and the predefined role db_ddladmin? It seems that the database properties permissions tab includes more permissions than the predefined role db_ddladmin.
Does anybody know the difference in terms of permission?
View 1 Replies
View Related
Mar 16, 2015
I have an ODBC connection string that is working fine with the following properties:
Database="XXXXXXX",Network="YYYYYY"; strangely no server is specified in the string, but it is specified in the ODBC Connection file.
I am trying to do a new server registration in SSMS for this database.However, I don't understand where the network spec is placed.
Under Registered server name I've tried:
YYYYYYXXXXX
When I browse the server for the database instance list, I receive "network path was not found".
I even tried:"XXXXXXX",Network="YYYYYY" for the registered server name.Same error message.
What am I doing wrong ?
View 1 Replies
View Related
Jul 26, 2006
Query #1:select <list of fields>from Cjoin B on C.b_key = B.b_keyjoin A on B.a_key = A.a_keywhere A.o_key = <some value>Query #2:select <list of fields>from Cwhere b_key in (select b_keyfrom Bwhere a_key in (select a_keyfrom Awhere o_key = <some value>))#1 (and other things with the same general pattern) are used inliterally thousands of places in this one client's system, and ismuch nicer to write, but seems to be rather slower than #2. Isthere any way to tweak the tables to tell the system somethinglike "hey, B, whenever you're joined to A, you should seriouslyconsider waiting for A to be filtered down to a manageable levelfirst"? And similarly for C/B.MS SQL 2000, SP3, 6.5 compat mode. These are set in stone untilwe upgrade the accounting software (highly non-trivial).
View 1 Replies
View Related
Oct 31, 2007
is there anyway to retrieve the database properties and display in an asp.net web form as a quick over view rather than logging onto the server to check, items such as size, space available, last backup data etc? Cheers
View 2 Replies
View Related
Sep 28, 2007
When I create a new database it asks for information regarding the Data files.
What is it for? Will this file get creted to the location that I write?
How often will it get copied there?
View 1 Replies
View Related
May 9, 2007
Hi.
I am trying to look for ways on how to get all the databse properties under each instance.
say I want to view the size, recovery model, physical location, etc.
The sys.database_files is only applicable per database. Is there a query, or a table in master database to get these or store these values?
thanks...
View 3 Replies
View Related
Mar 4, 2008
I am trying to find information on Database properties - more specifically, what the 'Space Available' means, because right now when I look at the properties of a database I am setting up, which will grow substantially I am sure, it says the database size is 154 MB and the Space Available is 42 MB. What is the Space Available? What does this refer to and is it going to be restricting the growth of the database? Thanks.
View 7 Replies
View Related
Nov 19, 2004
Hello, Everyone:
There are some databases in a SQL Server. How to check,
1. User database numbers
2. Backup plan
3. Configuration
4. User database size
5. Nightly process
Thanks a lot
ZYT
View 1 Replies
View Related
Dec 17, 2007
database role properties/ permissions
when i click permissions it takes forever to pop up the window.
Anyone know why and how do i make it faster.
http://www.sqlserverstudy.com
View 5 Replies
View Related
Nov 27, 2006
Hello there,I have about 30 sql servers runnign multiple databases amd i need toaudit them so what i want to do is to log onto each server and thenmaybe export the database properties into an excel sheet or text file.Anyone know how to do that i am quite new to sql any help will beawesome.Regards and thanx in advance
View 2 Replies
View Related
Sep 24, 2007
Howdy folks!
If I set the following three properties when creating a database, do I need to set the same properties when I open the same database later?
sscedbprop[0].dwPropertyID = DBPROP_SSCE_AUTO_SHRINK_THRESHOLD;
sscedbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[0].vValue.vt = VT_I4;
sscedbprop[0].vValue.intVal = 100;
sscedbprop[1].dwPropertyID = DBPROP_SSCE_MAX_DATABASE_SIZE;
sscedbprop[1].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[1].vValue.vt = VT_I4;
sscedbprop[1].vValue.intVal = 4091;
sscedbprop[2].dwPropertyID = DBPROP_SSCE_ENCRYPTDATABASE;
sscedbprop[2].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[2].vValue.vt = VT_BOOL;
sscedbprop[2].vValue.boolVal = (a_bEncrypted==TRUE)?VARIANT_TRUE:VARIANT_FALSE;
Thanks!
BTW, why is the max size not 4096?
View 3 Replies
View Related
Aug 14, 2006
Due to a network time skew some time ago, all of the databases on an SQL system now have the following information stamped on them..
Within Enterprise Manager (SQL 2000), The "Last Database Backup" timestamp for each database is showing the year 2025.
Our backup system runs incrementally daily and a weekly full. Unfortunately, the timestamp does not seem to be updating as the year is as I guess in the future.
Can anyone advise of a procedure so as I can wipe this time skew date from each of the databases?
Many thanks folks!
View 4 Replies
View Related
Aug 10, 2012
I am trying to generate a report from the database. I am using Visio 2010. I would like to include the table names, column names , column types and the comments that I entered in the NOTES field associated with the tables and columns. Basically, I would like to generate a data dictionary report.
View 2 Replies
View Related
Dec 6, 2007
I have added alias datatypes using sp_addtype and would like to view the properties defined in it.
please let me knowhow to view the properties of them and also is there a way to list all alias datatypes existing in the current database along with their properties.
View 1 Replies
View Related
Oct 31, 2007
Can anyone help?
When I select the database and right click. I receive the following error:
Cannot show requested dialog
Additional Information:
Cannot show requested dialog (sqlMgmt)
Property Owner is not available for Database '[DB_name]'. This property may not exist for this object,
or may not be retrievabledue to insufficient access rights. (Microsoft.SqlServer.Smo)
I am using SQL server 2005.
View 4 Replies
View Related
May 19, 2015
We have 1000s of SSRS reports hosted on the SQL 2008 R2. All reports are supposed to have all parameters "hidden". Random reports have been reported to expose the parameter when developers accidently deploy to test servers. Is there a way to identify the reports and the parameter property (Hidden, Visible, Internal) from the Report server Database?
I checked executionlog2 but can not find the parameter property.
View 7 Replies
View Related
Mar 27, 2006
I have just upgraded to SQL Server 2005 from SQL Server 2000.In Microsoft SQL Server Management Studio, when I click on databaseproperties, I receive the following error:-Cannot resolve the collation conflict between"Latin1_General_CI_AS"and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.(Microsoft SQL Server, Error: 468)Some reference suggest that I can change the database collation byclicking database properties!What can I do?
View 21 Replies
View Related
Jul 13, 2007
I installed SQL Server 2005 Developer Edition. When i create a new database (using the "New Database" dialog) i cannot set the new database's compatibility level to "SQL Server 2005(90)" because this option is not in the "dropdown list". the only items shown are: "SQL Server 7.0(70)" and "SQL Server 2000(80)". I set the owner to "sa". How do i get "SQL Server 2005(90)" in my "compatibility level" drop down list? Is this an installation option that i missed? Thanks in advance for any assistance!
-chris
View 12 Replies
View Related
Mar 3, 2008
Hi all,
I'm using VS2005 + SQL Compact 3.1 and Enterprise Library 3.1 May 2007
I can successfully create a db using pull method, when I try to push back data I got the following SQLCE error:
"Another user has opened the database with different instance-level initialization properties. [ 32 ]"
Before calling the push method I did
SqlCeDatabase eCE = Helper.GetDB() as SqlCeDatabase;
eCE.CloseSharedConnection();
SqlCeConnectionPool.CloseSharedConnections();
SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess();
rda.InternetUrl = webSynchronizationUrl;
rda.InternetLogin = webuser;
rda.InternetPassword = webpass;
rda.LocalConnectionString = eCE.ConnectionStringWithoutCredentials;
but after the assignment the LocalConnectionString is different from ConnectionStringWithoutCredentials, there are much more ssce parameters.
What I don't understand is:
why I'm getting this error even after closing the connection ?
Since the error report [32] the ssce conflicting parameters shoud be AUTO_SHRINK_THRESHOLD, FLUSH_INTERVAL and MAX_DATABASE_SIZE
I tried to cut & paste the same values from LocalConnectionString into app.config connection string, at first It seemed to work, but now I got the same error.
Any help will be greatly appreciated
Thanks
View 3 Replies
View Related
Nov 2, 2007
Hi all,
Can we get the event properties by using a query?
Are there any extended stored procuder to get the above?
Scenario:
>Desktop>Right Click on My Computer
>Go to Manage and click
>Expand System Tools
>Expand Event Viewer
>Application
click on one event.We can get the log info which is the manual procudure.
But now i want to get the event properties through the Query analyzer...
Any help would be great?
Thanks,
View 4 Replies
View Related
Mar 25, 2008
Hi 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
View 4 Replies
View Related
Mar 9, 2006
Hi,
Does anyone has query to give all table sizes on a database?
Appreciate your help.
Thanks
View 2 Replies
View Related
Apr 10, 2008
I am trying to use the Import Wizard to setup a daily job to import new records based on an ID field (PK). The source database is remote and a replica. I am inserting new records to update my table called the same thing. Both are SQL Native Client
Code Snippet
select *
from [CommWireless].[dbo].[iQclerk_SaleInvoicesAndProducts] as S1
join [IQ_REPLICA].[dbo].[iQclerk_SaleInvoicesAndProducts] as S2
on S1.SaleInvoiceID = S2.SaleInvoiceID
where S1.SaleInvoiceID > S2.SaleInvoiceID
When I parse the query, I keep getting an error message.
Deferred prepare could not be completed.
Statement(s) could not be prepared.
Invalid object name 'IQ_REPLICA.dbo.iQ_SaleInvoicesAndProducts'. (Microsoft SQL Native Client)
Anyone know an easy why to get this to work? Or should I add a create table to verify new records?
View 8 Replies
View Related
Oct 19, 2000
Hi and thanks for your help
specifying the default database means what. for instance when I add alog in user and I have to choose a default database. Does it make a difference if the Default database is Master or any other user database name. What is the significant to select Master vs any other database.
Thanks,
Ahmed
View 1 Replies
View Related
Oct 11, 2002
Hello!
We are having problems with a single SQL Server where the Server properties will not save, and always revert to the default.
It occurs only on those tabs with "configured" and "running" options at the bottom. Other tabs will keep the settings we custom.
The big problem is with the memory and processor tabs which even when we've made only a single change and stopped and started the service, reverts back to a default.
Any clues - we are logged in as "sa" and have not removed the SA priviledges.
Thanks for any help,
Simon
View 1 Replies
View Related
Apr 4, 2003
Once upon a time, I went to check the properties of an instance and EM just hung - anyone know why this might happen?
View 1 Replies
View Related
Aug 15, 2002
Hi all,
Plz help me to know whats wrong with following script:
EXEC sp_addextendedproperty
@name = 'col_Programs_ID_Description',
@Value = 'The Unique Identifier for a specific Program',
@level1Type = 'Programs',
@level2Type = 'ID'
I get the following error:
Server: Msg 15600, Level 15, State 1, Procedure sp_addextendedproperty, Line 42
An invalid parameter or option was specified for procedure 'sp_addextendedproperty'.
NOTE: If I dont use the last 2 parameters this procedure runs fine, but it adds the Extended Property to the Database Level while I'm trying to add it to the TABLE-->COLUMN level.
Thanks
Mariah
View 2 Replies
View Related
Dec 30, 2004
Hi all,
I'm having a problem need your help:
I can see all properties of datafile (FilegroupName, FileID, FileType,
Location, CurrentSize, Space Used) by Enterpise Manager.
Please tell me how do I see all those properties by SQL Query Analyzer.
I want to know actual space used in data file to shrink file.
Thanks in advanced,
TN
View 5 Replies
View Related
Jan 11, 2005
Running SQL2000 SP3.
We installed an application named Streamserve. Now, if I go into Enterprise Manager I can expand the databases fine. But when I try to right-click properties Enterprise Manager goes bye bye. No error messages. I tried this on master and same thing. I currently have 2 different machines - a dev and a QA box and both are behaving badly.
Any ideas?
Thanks
Sharon
View 4 Replies
View Related
Apr 25, 2008
Hi,
when ever I try to look at the properties of a job, the create new job window appears. what gives?
View 7 Replies
View Related
