Retrieving Information About All Indexes In Database
Sep 8, 2003Hi,
I want to get information about all the indexes in a database.
Can any one suggest me a query for this.
Thanks.
ADVERTISEMENT
Retrieving Constraint Information
May 14, 2004Hello
I wrote this query to retrieve all constraints (primary keys, foreign keys, unique keys, checks) on a table.
SELECT
c.name AS name,
CASE
WHEN c.xtype = 'PK' THEN 'primary'
WHEN c.xtype = 'F' THEN 'foreign'
WHEN c.xtype = 'UQ' THEN 'unique'
WHEN c.xtype = 'C' THEN 'check'
END AS type,
tkt.name AS contable,
tkc.name AS confield,
fkt.name AS reftable,
fkc.name AS reffield,
com.text AS expr
FROM
sysobjects c
LEFT JOIN sysconstraints con ON con.constid = c.id
LEFT JOIN sysforeignkeys fks ON fks.constid = con.constid
LEFT JOIN sysobjects tkt ON tkt.id = con.id
LEFT JOIN syscolumns tkc ON tkc.id = tkt.id AND tkc.colid = con.colid
LEFT JOIN sysobjects fkt ON fkt.id = fks.rkeyid
LEFT JOIN syscolumns fkc ON fkc.id = fkt.id AND fkc.colid = fks.rkey
LEFT JOIN syscomments com ON com.id = c.id
WHERE
c.xtype IN ('PK', 'F', 'UQ', 'C')
AND tkt.name = '$table'
AND c.name = '$constraint'
It returns a row for each constraint which can be easilly stored in an associative array.
Array (
[name], //name of the constraint
[type], //(primary|foreign|unique|check)
[contable], //table the constraint is on
[confield], //field the constraint is on
[reftable], //referenced table, null if type!=foreign
[reffield], //referenced field, null if type!=foreign
[expr], //check expression, null if type!=check
)
Everything works as expected except for one issue. For primary keys and unique keys, the sysconstraints.colid field is always '0'. The sysconstraints.id field properly indicates the id of the table that the primary/unique key is on, however I have no way of knowing which column(s) the primary/unique key is on. According to the Transact-SQL reference, the sysconstraints.colid field is the "ID of the column on which the constraint is defined, 0 if a table constraint.". Therefore, it looks like primary/unique constraints are stored as table constraints instead as a primary/unique constraint. However the sysconstraints.status field indicates the type of constraint to be a primary constraint or a unique constraint, not a table constraint.
Pseudo-bit-mask indicating the status. Possible values include:
1 = PRIMARY KEY constraint.
2 = UNIQUE KEY constraint.
3 = FOREIGN KEY constraint.
4 = CHECK constraint.
5 = DEFAULT constraint.
16 = Column-level constraint.
32 = Table-level constraint.
Is there something I am missing? Or maybe there is a better way to find the columns of a primary key or unique key that I can integrate into my above query?
Thanks
-except10n
Retrieving Indexes Info
Jun 19, 2006Hi,
Is there any way to retrieve index information for a database through a SQL query?
I usually query out tables structure, et al using INFORMATION_SCHEMA. Is there anything like that for indexes?
Thanks,
Paul
Retrieving Form A Text Box And Using Information In SQL Statement
Mar 8, 2008Hi everyone,
So what I am trying to do is to have a simple textbox where the person enters a code.
Then I need to have that code in an SQL statement like: SELECT Participant_Code FROM Contacts WHERE (this is where im stuck, I need to have something like WHERE Participant_code = code (code is the textbox ID))
After this is done I need to keep this code from page to page, because I will need to add data from other textboxes to the database.
Thanks
System Information About Indexes
Jan 17, 2006Hi,Is het possible to find information about indexes in SQL Server 2000?Information like pad index, fill factor, index fields, SortinTempdb?Gr,Hennie
View 3 Replies View RelatedAn Error Occurred While Trying To Access The Database Information. The Msdb Database Could Not Be Opened.
Jun 21, 2007I'am doing functionality test on DTS packages and saving my DTS packages to meta data services instead of saving them as local packages. We would like to see what information would be provided by saving them this way, but when we try to open the meta data browser (the 3rd icon under DTS) we get the following error:
An error occurred while trying to access the database information. The msdb database could not be opened.
DB Design :: Buffer Database - Insert Information From Partners Then Make Update To Main Database
Oct 29, 2015I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
View 6 Replies View RelatedRetrieving PDF From MS Sql Database Using C#
Sep 24, 2007I am now retrieving the PDF from the database and I am getting an error:
Error 1 'GetImages.GetImage(int)': not all code paths return a value
I have:
int imageid = Convert.ToInt32(Request.QueryString["ACTUAL_IMAGE_PDF"]);
And...
private SqlDataReader GetImage(int imageid)
{
Sql Statement...
}
Could someone help please??
Retrieving Image From SQL Database
Jul 3, 2007Ok, again, I'm reasonably new to this. I've been trying to display an image stored in SQL in a ASP.NET page. Pretty simple stuff I would have thought. I've read countless examples of how to do this online, and many of them use the same method of displaying the image, but none seem to work for me. The problem seems to lie in the following line of code:
Dim imageData As Byte() = CByte(command.ExecuteScalar())Which always returns the error: Value of type 'Byte' cannot be converted to '1-dimensional array of Byte'.Here's the rest of my code, hope someone can help. It's doing my head in!
Imports System.Data.SqlClient
Imports System.Data
Imports System.Drawing
Imports System.IOPartial Class _ProfileEditor
Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Get the UserID of the currently logged on user Dim NTUserID As String = HttpContext.Current.User.Identity.Name.ToString
Session("UserID") = NTUserID
Dim Photo As Image = NothingDim connection As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
Dim command As SqlCommand = connection.CreateCommand()
command.CommandText = "SELECT Photograph, ImageType FROM Users WHERE UserID = @UserID"command.Parameters.AddWithValue("@UserID", NTUserID)
connection.Open()Dim imageData As Byte() = CByte(command.ExecuteScalar())Dim memStream As New MemoryStream(Buffer)
Photo = Image.FromStream(memStream)
End Sub
End Class
Retrieving Image From Database
Sep 22, 2007Dear Friends,
I have read many solution over the net, but since I am unable to utilize anyone according to my needs I am seeking help from you people.
I have a table imagedata in sql server 2005 having fields name and imageperson. Name is string and imageperson is Image field. I have successfully stored name of the person and his image in database.
I have populated the dataset from codebehind and bind it to the repeater.
By writing <%# DataBinder.Eval(Container.DataItem, "name")%>I am a able to retrieve the name of the person. But when I pass photodata as
<%#photogen((DataBinder.Eval(Container.DataItem, "imageperson")))%>
where photogen is function in code behind having structure
public void photogen(byte[] dataretrieved)
{
Response.BinaryWrite(datarerieved)
}
But it is giving error at <%#photogen((DataBinder.Eval(Container.DataItem, "imageperson")))%>
The best overloaded method match for '_Default.photogen(byte[])' has some invalid arguments
AND
Cannot convert object to byte[].
Can anyone please provide me working solution with code for aspx page and code behind.
Thanks and regards
Retrieving A Value From A SQL Database In Code Behind
Nov 29, 2007VWD 2005 Express. I need to retrieve a value from a SQL database from the code behind a page and assign it to a variable. In Microsoft Access I can do this using the DLookup function. What I need to do is get the data that results from the following query into a variable:
SELECT [SystemUserId] FROM [SystemUser] WHERE ([Username] = @Username)
The name of the data source is SqlDataSource2
Also, in Access I can create a recordset from a query and then process through the recordset. Can that be done in VB code in VWD 2005 Express?
Retrieving Database DDl Commands
Mar 25, 2008Hi everyoneI created a database graphically in VS2005.Now I want the text version of those DDL(create) commands. Question is where and how are those commands stored in SqlServer I have the management studio too. Any ideas??
View 2 Replies View RelatedRetrieving Images From Database
Jun 10, 2008Hi.
I've been traling the forum and the internet for a striaghtr forward solution on this.
I'm trying to do what loads of people have already achieved: retrive images from an SQL database.
I have successfully uploaded inages to an "iamges" table, storing the image as SQL type "image", also storing size, type, description, name etc.
I want to be able to read one or more of these images (and other data) back from the table.
I'm using C#, ASP NET 2.0, SQL,
Any examples I've found needed a lot of tweaking, and for some reason or other didn't work!
Please don;t just pose yet another URL to an example - I've probably tried it and failed already.
I don't mind if I display the images in a GridView or just within an aspx page.
Ideally I'd be able to dispaly a few images on a page.
I know how to read and display the rest of the data in the "images" table.
Can anyone help please?
Do you need more info?
Thanks in advance,
Garrett
Image Retrieving From Database
Jan 16, 2004hi,
I have a SQL database that has Images stored. I am able to read the image from the database and display it. But i am not able to control the size and the position where it is displayed. I want to develop something like a photo album. the images should be displayed in a smaller size. but when the user clicks on the image, it should blow up to its original size.
I am struggling with this for some time. Any suggestions as how to develop it??.
Any help in this regard would be highly appreciated.
thanks,
-sriram
Retrieving All Tables For A Database
Nov 23, 2004hello
i want to know if there is any way to retrieve all the tables for a Database programmatically.
i want to connect to a server and select a database .then by selecting the database all it's tables be loaded into a ComboBox(for example)..the tables name i mean.
i want to give the user the option to select a table from a database.if it is possible help me
my programming language is C#.Net
any information is appereicated
Somayeh
Retrieving The 10 Min Entries From A Database
Jun 5, 2007Hi, I've been trying to write an sql statement that can output the 10 minimum entries from a database, but I can't figure out how to do it.
Any help?
Retrieving Data From Database...
Jun 4, 2006Hi all,
I am working on a project on PocketPC in which it is required to reteive data from database. I have created database on simulator as .sdf file. I want to retreive data from eVC++ code. How i can do so?
thanx
Saving Data And Retrieving It From The Database
Sep 18, 2007Heres my requirement from a financial analysis im doing...I have just calculated an industry averages on financial ratios...Now i wanna upload this industry average to the system...so that I can compare it to the individual companies' averages after calculating a particular company's average; meaning i wanna be able to call the industry average of a particular ratio (eg Current Ratio) after calculating a company's corresponding ratio...Is there a code fragment i can use for this ?? Thanks in advance...Adam
View 1 Replies View RelatedDoubt At Retrieving Database In Sqlserver2005
Oct 5, 2006hi,
here i had created a database named "books" and it contains 8tables in sql server 2005. now i want to take a copy of this "books" database in another system which also have sql server2005 as a back end to asp.net. so where i can get this "books". i.e in which folder it will be saved. is it possible to svae this databse or have to svae each queries by which i created tables?
One can never consent to creep,when one feels an impulse to soar
RAMMOHAN
Retrieving Symmetric Keys From Database.
Apr 9, 2007I've played with various configurations of the MS SQL Server encryption functionality, and come across an embarrassingly easy question that I cannot seem to resolve. How do I retrieve the actual symmetric and asymmetric keys out of the database?
I'd like to explore the possibility of off-loading the encryption/decryption work from the database server to a load-balanced pool of servers. For this model to work the pool would need access to the keys. The symmetric keys currently are generated with the command...
create symmetric key EncryptionKey211
with algorithm = AES_256
ENCRYPTION BY certificate CreditCardCert
Am I missing something obvious here?
Retrieving Code From A Trigger In The Database
May 12, 2006I have inherited a database and with it a set of triggers that are 'apparently' installed. However I have a number of different 'versions' for a specific update trigger and it is not clear from the files which is the latest, hence I am unsure of what 'version' of the trigger is installed in the database itself.
I am trying to retrieve the trigger code from the databse so that i can correctly determine which one of my trigger files is the latest version.
I don't want to end up in a situation where an earlier version of the update trigger is installed as this could wreck the underlying data.
I have tried using the syscomments.text column that relates to the trigger but it only gives me a concatenated version of the sql code and not enough to determine which file version was used for the trigger.
Is there a way of retreiving the entire SQL code that was entered rather than just the first few lines?
Retrieving Nearest Number From Database
Jan 7, 2008
OK, this is the scenario. I have a database with many columns ( each a mean value and a standard deviation, and with it a set of coordinates that i want to retrieve ).
Then i have a value that i want to query with the database, by comparing it with the mean and its standard deviation, and it should return a few sets (lets say 2) of coordinates whereby the the value of the mean is closet to the one in the database, in order of nearest value. How should i do it, since i am not using the exact value of the mean in the database?
I know its a bit confusing the way i wrote, but anyone understand wat i am trying to say and can help, i am very grateful. I had googled around for answers but cannot find. Thanks.
Error Message While Retrieving Table From Database
Dec 15, 2004well i have got a form which displays some result from the databasse, it runs fi9 in webmatrix, but when i try to run it from my main page and when i click on that button which should displays the aspx page, it gives me this error:
############################################################
Server Error in '/' Application.
--------------------------------------------------------------------------------
Login failed for user 'MUFADDALASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'MUFADDALASPNET'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException: Login failed for user 'MUFADDALASPNET'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +472
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +36
Microsoft.Matrix.Framework.Web.UI.SqlDataSourceControl.PopulateDataSet(DataSet dataSet, String listName) +494
Microsoft.Matrix.Framework.Web.UI.SqlDataSourceControl.GetDataSource(String listName) +51
Microsoft.Matrix.Framework.Web.UI.DataBoundControl.ResolveDataSource() +95
Microsoft.Matrix.Framework.Web.UI.DataBoundControl.DataBind() +59
Microsoft.Matrix.Framework.Web.UI.MxDataGrid.DataBind() +10
Microsoft.Matrix.Framework.Web.UI.MxDataGrid.OnPreRender(EventArgs e) +62
System.Web.UI.Control.PreRenderRecursiveInternal() +62
System.Web.UI.Control.PreRenderRecursiveInternal() +125
System.Web.UI.Control.PreRenderRecursiveInternal() +125
System.Web.UI.Page.ProcessRequestMain() +1489
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
############################################################
i have kept no passwords in my mssql server 2000 database
can anyone help me out with this problem
Retrieving Rights Of An User On All The Tables In A Database
Aug 8, 2001Hi,
How to ennumerate the Rights of an User for all the tables [Select/Insert/Update/Delete] in a database or how to ennumerate/list all the Table rights for a particular user in a database? By User, i mean the Login names [like bill, sam, sa] and not dbowner, public, etc. thanx in advance.
Retrieving 'CREATE' Statements For Database Objects
May 6, 2008MySql has a statement like:
SHOW CREATE TABLE tablename;
that returns the precise CREATE TABLE statement for the specified tablename.
Sql Management Studio also allows scripting Create Statement for any object by right-clicking it.
But I want to do this programatically, and fetch CREATE statements for Tables, Procedures & Views.
How can I retrive CREATE statements for Database objects progrmatically in Sql Server???
Switching Database's And Retrieving Table, Etc Info
Jul 8, 2007I have a query that gets the tables, columns, indexes, index_columns for all tables in a data base.
But, I need to be able to select any data base on the server
and then drill down into the tables, columns, etc.
I looked at sys.databases but can't see how to relate that to the
sys.tables to extend my query.
Is there a way?
I've looked at the information_schema area and catalog views
but I don't see a solution yet.
Retrieving The Tables Relationship From SQL Server Database Diagrams
May 26, 2004Hi All,
Currently i am defining a simple relationship between
Customers->Orders->Order Details through the Database Diagrams feature in
the SQL 2K. Using the Server Explorer, i can see the Database Diagrams, but
when i try to "drop" the Database Diagrams into the page, it gives the error
message.
I would like to know the procedures to retrieve the database relationships
from Database Diagrams and manipulate them through ADO.NET
I prefer to "convert" already defined relationship using SQL Server Database
Diagrams into XSD file or probably there is another method to "read" those
relationship and manipulate them.
Thank you very much for all your help
SQL Server 2014 :: Enquiry On Retrieving Data From Database
Jul 18, 2014I have a drop down menu for transmission. Inside the drop down menu, there are 1.Any 2.Auto 3.Manual options
Now I want to retrieve from my database, if the client select '1.Any' option. Meaning the output I want it to display all the products which have both auto and manual for transmission.
I have no problem retrieving all the products which either only have auto option or manual option.
Information Won't Post To My Database
Apr 15, 2007I'm having a weird problem with an easy process. I have a section that allows people to enter their name and email address if they wish to be contacted. I have it set up so that when they enter that information into the text boxes, the info is then sent to my SQL database. The code looks right to me, but it never comes up. Here is the code I have for the button_click.
Dim NTAdatasource As New SqlDataSource()
NTAdatasource.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()
NTAdatasource.InsertCommandType = SqlDataSourceCommandType.Text
NTAdatasource.InsertCommand = "INSERT into ContactUs (YourName, EmailAddress, DateTimeStamp) VALUES (@YourName, @EmailAddress, @DateTimeStamp)"
NTAdatasource.InsertParameters.Add("YourName", txtYourName.Text)
NTAdatasource.InsertParameters.Add("EmailAddress", txtEmailAddress.Text)
NTAdatasource.InsertParameters.Add("DateTimeStamp", DateTime.Now)
Dim RowsAdded As Integer = 0
Try
RowsAdded = NTAdatabase.Insert()
Catch ex As Exception
Server.Transfer("problem.aspx")
End Try
If RowsAdded <> 1 Then
Server.Transfer("problem.aspx")
Else
Server.Transfer("success.aspx")
End If
End Sub
The strange thing is that when I debug, the "rowsadded" value comes up to 1. The process runs through debugging just fine and redirects me to my "success.aspx" page. What am I doing wrong?
Thanks
Database Toolbar Information
Apr 5, 2001I work with multiple servers and I have system administrator rights on all server. My question is, why on some server when I click on a database it shows the General, Table & Index, Space Allocated tool bar, than if I go to a different server, click a database it does not show? I would like to see this toolbar on all my servers when I click a database.
Thanks Reggie
Sql Query For Getting Information From Different Database
Feb 18, 2003I want to write a sql query for information from a table in different database but on the same server.
what is the syntax.
select * from databse2.owner.table ??
i am in application1 database and i want to query from application2 database on the same server.
use application1
go
Select * from application2.dbo.tablename
go
Is this query correct?
Query Database Information
Oct 11, 2001Pardon the possibly senseless question - I have been an NT Administrator for some years, and have just gotten thrown into picking up some SQL DBA work and I'm still feeling my way around.
Is it possible to query the Master database for the setup information (etc) on the other databases? Several have been marked suspect due to a hard drive failure and I am trying to figure out what the original setup of the databases was.
Thanks,
Mary Elizabeth
NW Natural
Database Information Report
Jul 17, 2007Hello,I am trying to use the SQl Server database reporting tools to create areport on the database to find areas for optimization including tableformats, column formats, and sizes. Can anybody please help me withthis. If any body has used any other tool by which I can create abovementioned reports plz do let me know. I will be really thankful foryour help.ThanksSuneel
View 1 Replies View Related