|
Hi All,,
I Want To Get All Foreign Key From A Table And the Referenced Table Of This Foreign Key,,
How I Can DO That???
Thanks All;;;;
|
|
|
|
|
Look up INFORMATION_SCHEMA.TABLE_CONSTRAINTS in BOL.
Someone's gotta be the last to know, but why is it always me?
|
|
|
|
|
SELECT K_Table = FK.TABLE_NAME,FK_Column = CU.COLUMN_NAME,PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
ORDER BY
1,2,3,4
|
|
|
|
|
Hi i am getting following error in error log files of iis.can anybody tell me about this.
The client was unable to reuse a session with SPID 628, which had been reset for connection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
|
|
|
|
|
Hi,
Is it possible to have the following in sql?
IF @VarParam > 0 Exec(SELECT * FROM #TEMP1)
ELSE
Exec
(SELECT * FROM #TEMP1
UNION
SELECT * FROM #TEMP2)
Thank you
----------------------------------------------------------
Lorem ipsum dolor sit amet.
|
|
|
|
|
Hmmm... dunno, try:
EXEC (SELECT * FROM #TEMP1
UNION
SELECT * FROM #TEMP2 WHERE @VarParam > 0)
|
|
|
|
|
how to set sqlserver 2000 for accept remote connections ?
H.R
|
|
|
|
|
|
I saw many articles but i could not do that.
can any one help me with details ?
my application: VS.NET 2008 - C#
my DB: SQL Server 2000 personal edition
my windows: xp profesional sp3
H.R
|
|
|
|
|
Hi All,
Actually i have a problem in vista regarding a windows API SHOWWINDOW.
MY application is running in hide mode.I m Showing Window by using Findwindow() function.it runs successfully but sometimes in vista it runs successfully and software blinked in Taskbar. and it goes in background.
Best Regard
Anubhava Dimri
mailto: anubhava.prodata@gmail.com
|
|
|
|
|
If this is a database problem, I'm afraid I can't help you.
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
Dear All,
Is it possible to do one way replication using merge replication in MSSQL 2005?
if yes, plz provide me a reference.
thanks
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
i have solved the problem!
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
i am working on SQL2005.. i have installed Microsoft performance dashboard tool.
which let me know about cpu utilization graph on every second.
from last two days it showing me CPU utilization to 40%-50% and 10%-20% at some moment of time. but when it shows 40%-50% , there is no query in cache. on the other hand when it is on 10%-20% , there are so many queries in cache memory . and server computing power is fine in this case.
kindly tell me what is the reason behind this.?? and kindly let me know also how to make more and more queries to share the cache..
thanks in advance.
|
|
|
|
|
ASALAMOALEKUM ALL,
i have a following scenrio in my project:
I have two tables:
USER (user_id, user_name, course_id, course_name)
COURSE (course_id, course_name)
let suppose i have data in both the tables:
In USER:
user_id | user_name | course_id | course_name
--------------------------------------------------------]
1 | john | 1,2 |
2 | sam | 3,1 |
In COURSE:
course_id | course_name
----------------------------
1 | LAW
2 | HISTORY
3 | MEDICINE
now the issue is:
I HAVE TO WRITE A SQL QUERY OR STORED PROCEDUE WHICH INSERT course_name in USER table from COURSE table w.r.t course_id in USER table
i.e;
WHEN QUERY IS EXECUTED:
USER Table should be modified with:
user_id | user_name | course_id | course_name
--------------------------------------------------------
1 | john | 1,2 | LAW, HISTORY
2 | sam | 3,1 | MEDICINE, LAW
Please if anyone can help me... reply as soon as possible...
i am trying too but not getting any satisying way 
Thanks!
Take care
$@N@
|
|
|
|
|
the design of your database tables are making things difficult.
I advise you to create another table where you store the relationship between user and courses and hence separate the user table from the details of courses.
tb_users :
user_id | user_name
tb_courses :
course_id | course_name
tb_attendances :
user_id | course_id
Thus you do'nt have to suffer from the complexities of having to set a multivalued field.
Hope this helps.
Easy Profiler : a compile-time profiler for C++
www.potatosoftware.com
modified on Sunday, March 1, 2009 3:22 PM
|
|
|
|
|
Can anyone offer their thoughts/suggestions on how to best breakup scripts for managing a db? For instance, I'm starting a new project, and have already used SQL Server Manager to create the tables, pk's, indexes and the relationship between tables. Does this need to all be scripted in one large script?
Moving forward, any changes that I may apply to the db (e.g. new table, new index) will have to be saved to the db. Should I create scripts for any incremental changes? What is the best way to capture these changes? Is there something in sql server manager that will say "capture changes" and script just the changes??
I guess the advantage of scripting all of your incremental scripts would be to easily back out changes that may have caused problems with your code.
Do you create one giant script with a bunch of ALTER statements for your VIEWS, SP's, FUNCTIONS, etc...?
Thanks
|
|
|
|
|
You may want to look into SQL Triggers
|
|
|
|
|
You should think of your db scripts like source code and create a SQL folder in your source structure that you check in and out of your version control system. (You are using one ? Right ?) You can then see what the differences are between your 1.0 and 1.1 create table scripts are.
Then consider this:
1) Create a base set of scripts called "create_XXX" where the "XXX" is the database object like tables, views, trigger, sproc, index. These scripts should be designed so that you could create an entire database schema from a blank database instance.
2) Create a script called "upgradeSchema" which will contain all of the alter table, alter index, create view, etc that may have changed from one release to another. You should design this upgradeSchema script in such a way that it could be run more than once on a database and have no undesireable effects. In other words, make no assumptions about the current schema state, test to see if a column already exists, is the data in the column null, etc. Lots of error checking going on here ...
Also you may want to consider 2 other areas:
System data - This script would insert values into lookup tables in order for the system to function.
Sample data - This might be enough data in tables so that you can publish a student guide showing some basic operations of your application.
What I've presented isn't a complete solution, but it should give you something to think about ...
If you are not using a version control system, your asking for trouble. Look into Subversion http://subversion.tigris.org[^]
Or if you a small Windows shop, you could always use Visual Source Safe, from Microsoft.
|
|
|
|
|
Wow...you've definitely provided great examples!
1) Should the base set of scripts (Create_xxx) pretty much create the 'version 1.0' db? Then, I imagine I would run my alter scripts to get it updated to the current version?
2) Should I create one or multiple ALTER scripts for each release? For example, if my project has 8 releases, I probably should have 8 alter scripts right? Or should I simply have one version for my ALTER script and place everything in that one script.
3) Is there a way I can take my existing db and script it out within SQL Server mgr?
THanks!
|
|
|
|
|
1) Yes, the base set of scripts would be the 1.0 version. However, you can also think about the 2.0 version as being a new set of "create" scripts. Each time you have a major release (Ver x.0), the create scripts would be able to create a fresh schema. In a major release, you could have a conversion script like UpgradeSchema_v1_v2, which would take the system from any v1.x release and get them right up to a 2.0 release.
2) In the past, I have created 1 large ALTER script. I prefer to call it something like UpgradeSchema.sql for clarity. In this script you need to design it so that it can take a database from a v1.0 to a v1.1, v1.2, v1.3, etc in one shot. The script has to be designed so that it doesn't care which database version the system is at, perform enough checks so that nothing errors out. For example, if you need to add a column to a table, then check to see if the column already exists before adding it. You will become very familiar with the SYSOBJECTS table when writing these types of upgradeSchema scripts.
3) You can use the SQL server Management Studio to create your base scripts by highlighting each table, right click, choose "Script Table As, Create to, New query editor Window", this will give you a jump on creating your base set of scripts. Remember to create a separate set of scripts for each database object; one script creates all tables, another one creates views, etc ... You will see that running these scripts requires that they be run in a certain order otherwise they won't execute. For example, you can't create views unless you have already created table. 
More to think about.
|
|
|
|
|
Great..thanks for all your help!
|
|
|
|
|
I am new to SQL Server 2005...and was reviewing the following msdn url for creating logins and users: msdn.microsoft.com/en-us/library/aa337552.aspx
Can someone explain what the difference is between a db login and a db user?
From my understanding, you use the db login within SQL Server manager, or with your .NET code, correct? Then, you create a user and associate it to a login? Why? What's the relevance of knowing or having a user associated to a login, when the login is sufficient alone? Would I use the user logging into sql server mgr or within my .net code?
Do I need to associate a login with a user?
thanks!
|
|
|
|
|
A login connects you to the SQL Server instance. A database user defines what privileges a login has within a particular database. If you have multiple databases on your server, you probably don't want everyone who can connect to the server (login) have some kind of access to every database. So you could have a login associated with one database or with several.
Someone's gotta be the last to know, but why is it always me?
|
|
|
|
|
I am currently working in a MSSQL 2000 version and all of the paginated results for various scripts are done using 3 or more SELECT sub-queries.
Unlike MySQL which only requires a single query --- I cannot see this being very efficient, especially the larger your database grows?
I have done some googling and it turns out that row_count() is MSSQL answer since 2005 -- can anyone confirm whether this is the case and better yet refer me to some resources which I can read over the weekend to freshen up on MSSQL?
Cheers,
Alex
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
