|
I just want to double check if this script is on the right track.
select t.name, count(c.column_id) as columns
from sys.tables as t
inner join sys.columns as c on t.object_id = c.object_id
where schema_id = (select schema_id from sys.schemas where sys.schemas.name='EMR') -- Alter Schema name
group by t.name
order by [name] asc
Basically I want to check for altered tables between two different databases, this ought to pull out all the tables in a schema and the number of columns for each table. Obviously, any changes on the table that don't affect the number of columns, I will not be alerted of.
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
I prefer to use the information schema view - 'cause I can.
SELECT Table_Name, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'STG'
GROUP BY Table_Name
|
|
|
|
|
See, this is why I love this site 
I like your script better and will steal it!
Now , to figure out how to realize that there are changes!
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
hi guys
i want to upate sub string of one of my column
update tale1 set substring(orderno,6,2) = 'tt'
but above query is not working .
Tauseef A Khan
MCP Dotnet framework 2.0.
|
|
|
|
|
Do you get error message?
Explain more your question.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
1) Are you using transactions ? Make sure you have a "Commit" after your update statement.
2) Do you realize that in your example there is no "where" clause ? Every row in the table would be affected.
I believe the root of your problem is that you can't have a function on the left side of the assignment operator in the update statement.
The following code seems to work for me ...
select first_name from fsuser where user_code = 'DMUJICA'
begin transaction
update fsuser set first_name = substring(first_name,1,1) + 'XXX' +
substring(first_name,5,len(first_name)-4) where user_code = 'DMUJICA'
select first_name from fsuser where user_code = 'DMUJICA'
--commit
rollback
The output is:
DAVID
DXXXD
|
|
|
|
|
This is weird...  
You are setting a constant term with another constant..
If you write
Set substring(orderno, 6,2) = 'tt'
It means if order no is 12345678 then you write
78 = tt... 
You can only apply use functions on the left hand side when you are using a comparison...
In your case the query should be
update tale1 set orderno = substring(orderno,1,6) + tt + substring(orderno,8, len(orderno) - 8)
Remember, you are missing with where clause. So it will update every order no...
|
|
|
|
|
Hi, I really cannot understand your requirement.
What you are trying to achieve, is not at all clear in the statement presented here.
I fully agree with Abhishek.
However, if you invoke the statement "update tale1 set substring(orderno,6,2) = 'tt'" you will get the following error
"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','."
Now, I have a table say tblOrder with the following records.
OrderNo
------------
123456789
234567891
345678912
45678123
67
78
89
My goal is to update those orderno's that will satisfy the condition substring(orderno,6,2).
Means, if the order no is 987623456 , then substring('987623456',6,2) will yield me 34. So if any OrderNo 34 is present, that will be updated.
The query is
update tblOrder set OrderNo = 'tt'
where OrderNo in( select substring(OrderNo,6,2) from tblOrder)
The output is
OrderNo
------------
123456789
234567891
345678912
45678123
tt
tt
tt
Hope this helps
Niladri Biswas
|
|
|
|
|
hello there
i want to make a project about central blood bank which can connect to any other small blood bank in other hospitals and take information about available blood units thers and then any bank could connect to central blood bank and make request of the blood it need then the central bank will search in its database if it availabe in it it will reply if not it will connect to others hospitals and ask them about the request then when it find what ht need he will reply immediatle to the bank who request the blood and inform them of what he find. else we should care about doners and donation processes , test , etc.
i want to implement the project with asp.net and mysql for database
help me if any one know how to make it and please i want to get the database or ER-Diagram as soon as possible
|
|
|
|
|
Sounds like you don't have a clue. If you are being paid to do this, give the money back now. Alternatively, try a site like rentacoder.com and pay for it.
Seriously, there are so many things wrong with what you are asking for you have no chance of really doing it. For example, just think about the security issues of connecting to a hospital database.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
chuckle - pretty well what I told him over in the MySQL forum !
'g'
|
|
|
|
|
I'am glad I probably never gonna go to a hospital that uses your "system".
but anyway, here is a handy 10 points step by step guide
Step 0: Get a clue of what you are trying to do.
Step 1: Learn to code.
Step 2: Write a good system spesification, including databse design, software design, user interface and so on.
Step 5: Develope an prototype (Low-fidellity).
step 3: Choose an appropiate development methology for the prodject (eks. SCRUM, Extreme Programming, Rational Rose, Spiral model).
step 4: Choose an appropiate system structure (eks. n-tier, business Layer, MVC).
Step 5: Develope an prototype (High-fidellity).
Step 6: Implement the database design.
Step 7: write alot of code.
Step 8: Do alot of testing.
Step 9: Do alot of usability testing.
step 10. profit.
modified on Wednesday, September 2, 2009 7:59 AM
|
|
|
|
|
i want a to make a blood bank data base can one help me please
|
|
|
|
|
please any one have any idea for this send to me here or in my email to comunicat with him/here
|
|
|
|
|
Is this a school project or is this really gonna be a real worl app??
Are you a developer or you just have the idea??
if not a school project, are you getting paid for this??
Alexei Rodriguez
|
|
|
|
|
Hi,
I would like to export a database I use at work to test at home.
I'm wondering if there is a way to export it so the whole database is stored as a file, and you can connect to the file as if it was the SQL server (I cant access the SQL server from home).
Is something like this possible?
I am running SQL Server Management Studio 2005.
Cheers,
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
|
|
|
|
|
Hi,
you could use the SQL Server 2005 Express Edition at home. It is free to use... Then just create a complete backup at work and restore it at home.
Regards
Sebastian
|
|
|
|
|
Yes. Use the copy database wizard.
“If you think you are worth what you know, you are very wrong. Your knowledge today does not have much value beyond a couple of years. Your value is what you can learn and how easily you can adapt to the changes this profession brings so often.”-– Jose M. Aguilar
|
|
|
|
|
Just run backup...
Run this script in your office computer :
BACKUP DATABASE [db] TO DISK = N'c:\Backup\db.bak' WITH NOFORMAT, NOINIT, NAME = N'db-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
It will produce an output db.bak in your local C:\backup folder.
[Please replace db with your database name on the script.]
Now in home run this :
RESTORE DATABASE [db] FROM DISK = N'C:\Backup\DB.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
Here db is the name of the database it will create when it restores, place the db.bak file from your office to your home computer in C:\backup or whatever you like to and finally run it..
|
|
|
|
|
if you have good internet connection then you can connect remotely as well as by using sql express and their are also so many other Sql Management tools are availbale same like sql server management studio you can use them also and there are some hand tools for sql server datase copying /backup and emailing automatically
http://www.sqlbackupandftp.com/[^]
http://www.download3k.com/System-Utilities/Backup-Restore/Download-SQLBackupAndFTP.html[^]
while other regular options are their as well as normal backups and copy database , mirroring etc
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
Means he needs to expose the SQLServer browser from the firewall of the server... This would not be a good option I think.
|
|
|
|
|
if possible then he can add Execption depends upon cicrumstances and is one of the option if possible otherwise there copying or backup is good option and he can even export only some tables and if he can export tables into XML and then can import then it will be even another way to do it , depends upon the nature as far as my understanding is he is not using at clients he is using for his test pc at home
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
I have following two tables
1st table is Fee:
PK ---- payable
1 ---- 1500
2 ---- 1200
3 ---- 1900
2nd table is Fee_Detail:
pk ---- paid ---- fk_fee
1 ---- 700 ---- 1
2 ---- 400 ---- 1
3 ---- 400 ---- 1
4 ---- 800 ---- 2
5 ---- 300 ---- 2
Result should be
payable ---- paid
1500 ---- 1500
1200 ---- 1100
1900 ---- 0
what is sql query
modified on Wednesday, September 2, 2009 2:39 AM
|
|
|
|
|
You need and inner join a group by and a sum
Select FeeID, sum(payable), sum(paid)
from Fee
iner join Fee_detail FD on FD.fk_fee = fee.FeeID
group by
FeeID
|
|
|
|
|
I want to fetch all records of left hand side
and only sum of 'paid amount' matching records of child table(right hand side) according to fk_fee(foreign key column)..
for example
Fee table:
Pk ---- payable
1 ---- 1500
2 --- 700
Fee Detail:
Pk ---- Paid ---- fk_fee
1 ---- 1100 ----1
2 ---- 200 ----1
3 ---- 100 ----1
Result should be
payable ---- paid
1500 ---- 1400
700 ---- 0
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
