I need to get records from multiple databases.
In my main database, I have a list of databases related to seperate business units.
For each of those databases, I need to get a list of values from a table (the table exists in each database).
Basically
foreach database in a list
Do a Lookup
end
Possible?
hello anybody now
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.
can anybody help me.
thanx
kiran.
anyone know how to query a list of databases?
current the sp_helpdb seems cant help me, because it query all the details, i want to do it like select * from database where database id=2
I want to list my databases, and physical related files, and their size but the following commands do not provide the size. Any idea?
SELECT * FROM sysdatabases
SELECT * FROM sys.sysfiles
I know it's silly question
Canada DBA
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.
How can I, programatically, get a list of attached databases? I'm using VB.
View 4 Replies View Related
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:
<?xml version="1.0" encoding="utf-8" ?>
<Connection>workstation id=ASUS;packet size=4096;user id=sa;data source="5.15.183.222KWMSDE2"; persist security info=True;initial catalog=tsLocal;password=pass
</Connection>
Is there some special way that you connect to the server without specifying the database?
Is there a certain query I must use once I am connected? Can the information be returned in the form of a data table in a data set?
Thanks.
Hi,
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.
Thanks!
Joyce
Hi,
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
Any way to list all databases that a user has access to?
View 6 Replies View RelatedIs 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
View 2 Replies View RelatedI 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
View 7 Replies View RelatedHey 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!
View 6 Replies View Related
Is it possible to get an aggregate list of databases from multiple servers, without using SMO?
All target servers are running SQL Server 2005.
Ben Aminnia
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)
Next
3)
how can i ???
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.
Hi,
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.
Thanks.
I would like to SELECT all filegroup on an SQL server instance, is that possible?Or only per database?
View 21 Replies View RelatedHi
I have created a new login in SQL Server 2000.
I have hooked up to more than one database which creating the login.
In syslogins we can see only the default database associated with that login.
I want the list of all the databases for that specific login..
How could I get them?
-------------------------------------------------------------------------------
My main task is to create a login under a specific database.
For which I'm validating whether that login already exists or not I'm checking it in the syslogins table. I need to specifically check it is in my databse or not....for that what should I do...
Thanks in advance
I need to generate a list of databases, jobs, DTS packages, Linked servers, and users.
Is there a simple script or procedure for this?
Thanks
I am using SQL Server Express 2008 Management Studio & in the databases list are some databases that are non existent (have been deleted in Windows Explorer). How do I remove them as trying to delete when logging in using Windows authentication fails?
View 16 Replies View RelatedLooking for query that lists all databases, in an instance, that are not accessed before a given date (e.g., not accessed before December 31, 2014)?
View 6 Replies View RelatedSo at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
HI...
I need to do a simple task but it's difficult to a newbie on ssis..
i have two tables...
first one has an identity column and the second has fk to the first...
to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !!
i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"
here is the script
INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@@Identity,
?,
?,
?
)
what's the problem ??
Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Can anyone help?
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
How can I do this?
Insert Query:
insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE)
SELECT DISTINCT
tri_IDENT.IDA AS PPID,
tri_Ldg_Tran.CLM_ID AS PTicketNum,
tri_ClaimChg.Line_No AS PLineNum,
tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT'
THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier
ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate,
tri_Ldg_Tran.Tran_Date AS PEntryDate,
tri_ClaimChg.Hsp_Code AS PHCPCCode,
tri_Ldg_Tran.Adj_Type,
tri_Ldg_Tran.PRS_ID,
tri_Ldg_Tran.Create_Time,
tri_Ldg_Tran.Adj_Group,
tri_Ldg_Tran.Payer_ID,
tri_Ldg_Tran.TRN_ID,
tri_ClaimChg.Primary_Claim,
tri_IDENT.Version
FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN
qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc =
qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN
tri_Ldg_Tran RIGHT OUTER JOIN
tri_IDENT LEFT OUTER JOIN
tri_ClaimChg ON tri_IDENT.Pat_Id1 =
tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID =
tri_ClaimChg.PRS_ID AND
tri_Ldg_Tran.Chg_TRN_ID =
tri_ClaimChg.Chg_TRN_ID
AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN
tri_Payer ON tri_Ldg_Tran.Payer_ID
= tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type
= tri_Ldg_Tran.Pay_Type AND
qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type
WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg')
AND (tln_PaymentTypeMappings.PTMClientCode = 'SR')
AND (tri_ClaimChg.Primary_Claim = 1)
AND (tri_IDENT.Version = 0)
Hi,
I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.
Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'
My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.
performance is a not that great so I'm thinking that my code can be improved.
SQL Express 2005 SP2
Windows Server 2003
1GB Ram....(I know, I know)
TIA
Code Snippet
GO
CREATE TABLE [dbo].[Tokens](
[TokenID] [int] IDENTITY(1,1) NOT NULL,
[Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED
(
[TokenID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens]
(
[Token] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE FUNCTION [dbo].[SplitTokenList]
(
@TokenList varchar(max)
)
RETURNS
@ParsedList table
(
Token varchar(255)
)
AS
BEGIN
DECLARE @Token varchar(50), @Pos int
SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ','
SET @Pos = CHARINDEX(',', @TokenList , 1)
IF REPLACE(@TokenList , ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1)))
IF @Token <> ''
BEGIN
INSERT INTO @ParsedList (Token)
VALUES (@Token) --Use Appropriate conversion
END
SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos)
SET @Pos = CHARINDEX(',', @TokenList, 1)
END
END
RETURN
END
GO
CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList]
@TokenList varchar(max)
AS
BEGIN
SET NOCOUNT ON;
create table #Tokens (TokenID int default 0, Token varchar(50))
create clustered index Tind on #T (Token)
DECLARE @NewTokens table
(
TokenID int default 0,
Token varchar(50)
)
--Split ID's into a table
INSERT INTO #Tokens(Token)
SELECT Token FROM SplitTokenList(@TokenList)
BEGIN TRY
BEGIN TRANSACTION
--get ID's for any existing tokens
UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0)
FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token
INSERT INTO Tokens(Token)
OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens
SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0
return the list with id for new and old
SELECT TokenID, Token FROM #Tokens
WHERE TokenID <> 0
UNION
SELECT TokenID, Token FROM @Tokens
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @er nvarchar(max)
SET @er = ERROR_MESSAGE();
RAISERROR(@er, 14,1);
ROLLBACK TRAN
END CATCH;
END
GO
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.