Hi,
I need to list all the tables in northwind database.
I'm using sql server 2000.
In query analyser i'm in master database.
I don't want to change the database to northwind.
How to list all the tables in northwind database when the database
combobox has master in query analyser.
I want to run this query with database name.I won't want to run for
current database.
Kindly help me to solve the problem.
How can I create a master list of a particular column, in other words is there a SELECT query I can do that would bring back one of every type of? here's an example: calls table -- bring back one of every kind of call_descr thx for any help.
I am struggling trying to clean some data and identify duplicate records. I used fuzzy grouping in SSIS and provided back a series of groups. The issue is some of the individual records can appear in multiple groups (so in reality the groups should be combined). This is best explained with an example:
Original Data key1 key2 647942600014 647942285437 2324662490640 2324662285437 2324662066128 2222 2285437 2222 1111111 9999 1111111 9999 2222222
I only choose 2222 as the surviving key because it was the smallest number. I really do not care which number remains as long as it is the same across.
I tried playing with self joins between the tables but have had no success.
I am using Sql Server 2008 and the number of records could 500K to 1MM.
I need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?
I have a fairly complex report that was given to me that was hard coded for single parameters. There is a dropdown for each market (created from a query in SSRS). The users have to run for each market each week.
Is there a way to use this report as a Sub-report inside a list of a master report and then use a mult-value parameter?
I want this multi-value parameter to build the values for the list and then run the "sub-report" for each value.
I'm looking for a way to list the target servers associated with a master server. The reason is that we're moving to another master server, and I'd prefer not to move the targets manually.
I've got most of the T-SQL already (sp_msx_enlist, sp_add_jobserver), but I'd like a scripted solution instead of a wizard.
Version: 10.00.1600 (I believe the first 2008 release)
I made a backup from the master, model and msdb system tables. Now I travelled to a different country, and I only have the backups and would like to access them. I was able to restore the master db to a new installation of mssql but, it wont start. I do know why: as I read after I quickly realised that master backups take the accounts, serverID, groups, directory structure, and lots of other things.Basically I have no chance of doing a master restore. The question here: Is there any way of getting my data out from the master backup file (thats where my stuff is)? I'm thinking of "restoring" it to a separate non-master db, table, anything. I know now that I did the most stupidest thing ever, but believe me I don't want the last half years work to just disappear.
We need to load a "master" flat file to SQL Server tables. The file is a dump from mainframe. Based on a field called "record_type", each record in the file has different columns. I would use the following as an example (the real file is much more complicated than this, but you get the idea):
If "M", the fields are "age", "gender", "birthdate", "state", "salary"
If "F", the fields are "age", "gender", "birthdate", "state", "company", "salary"
We need to load the file (only one file) into two different tables, M_table, and F_table. But I have researched and discovered in DTS the source (TEXT file) can not be queried against to filter on the gender field.
Since each record may have different number of fields, I cannot really load the flat file into a "staging" table.
Does anyone has any idea on how to achieve this? Thanks in advance!!!
I'm running a master package executing 8 child packages.
Each package contains the same connection managers and each package is stored within the MSDB database. The master package executes the packages stored in the MSDB using the 'execute package task'.
When running the master package from either MSDB or visual studio directly the odd thing happens that some tables will be filled, but after package execution I notice each table to be empty. There's no rollback 'procedure 'specified within the package and each package executes successfully because of error row handling. Anybody any hints how to solve this one?
In my DataFlow i have OleDBDataSource and OleDB Command. Using these i am inserting data to master and child tables.
In OleDBDataSource , i am inserting into master table and returning the ID of newly inserted rows. Next in the OleDB Command, i am inserting to child table using the ID returned from OleDBDataSource.
It is working fine. Now i want to put this in the Transaction so that if it fails to insert into child table, the changes made to the master table should be rolled back. I tried by giving Transaction Supported for dataflow. But does not looks like it works for me. Please suggest me the best approach for this.
I recently moved the db to a new server using detach and reattach sproc. However the moved db in its new location does not have any of the tables or sprocs that I created. As I understand it, information on the databases on the server is stored in the master db. Could it be that the tables are not showing up because the master db on the new server knows nothing of the new db? Must I also copy the master from the other server, perhaps? Has anybody come across this before? TIA D. Lewis
I need to write a sql query which is a master-detail query. Here's the example structure of tables:
Master table: ColID as longint, ColA as int, ColB as int, ColPartID as longint, ColPartName as longint
Child table -- Wheel: ColID as longint, ColA as int, ColB as int Child table -- Door: ColID as longint, ColA as int, ColB as int Child table -- Window: ColID as longint, ColA as int, ColB as int ..... etc
From the master table, it needs to join with its child in order to get the detailed information. However, there're more than one child table for it to join. In other words, the query has to choose the correct child table to join for each row selectively. The use of correct child depends on one of the columns in its master table (ColPartName).
My question is: Does it worth of me keep finding a solution for this query or should I abandon this? I really need some advice, please.
I just notice that my MASTER database has some user tables and user SP ..and I am thinking to move them to 1 new user database but I am worried it will break something ..
What should I do ?
Moreover I wonder why Transaction log of MASTER can be full ( The recovery model is simple ) It should be fine , isn’t it?
Environment is SQL Server 2000 64 bit.I restore from a script 'my' database, this works fine. However, allthe tables are also found in master, no data though.Anyone experienced this?
I need some help for designing the IDs / Primary keys for some master tables in my database. Following are master tables. Client_Master, Buyer_Master & Seller_Master; I want to set Client_Id, Buyer_Id & Seller_Id as their respective primary keys and they should have following properties
Client_Id :- a) should be auto-incrementing value, b) unique & c)should be of the format – CLXXXXXX, where “CL” {Constant start characters} & “X” {any number 0-9} Similarly:: Buyer_Id :- BYXXXXXX Seller_Id :- SLXXXXXX
We are implementing the database in MS-SQL 2005 & MySQL
Can anyone help me find a solution to this, especially in MS-SQL.
I have 6 tables which are very huge in row count and need to be partitioned for better manageability.
Little info: Every day, 300 Million records are inserted and 300 million records are deleted in below 7 tables. we maintain only 8 days worth of data in below tables which is the reason records which are older than 8 days are continuously deleted.
Master table which has [ID],[Timestamp] Table Name: Sample - 2,578,106
Child tables: Foreign key [ID] is common for all the tables. There is no timestamp column in child table. dbo.ConnectionDB - 1,147,578,048 dbo.ConnectionSS - 876,458,321 dbo.ConnectionRT - 118,133,857 dbo.ConnectionSample - 100,038,535 dbo.Command - 100,032,235
I would like to partition the above child tables based on the IDs that are inserted every 4 hours. Meaning, All IDs that are inserted in 4 hours window should be in a partition.
I have 6 tables which are very huge in row count and records needs to deleted which are older than 8 days.
Little info: Every day, 300 Million records are inserted in below 7 tables. we should maintain only 8 days worth of data in below tables. How to implement Purge script which can delete records in all tables in the same time and with optimized parallelism.
Master table which has [ID],[Timestamp] Table Name: Sample - 2,578,106
Child tables: Foreign key [ID] is common for all the tables. There is no timestamp column in child table. So the records needs to deleted based on Min(ID) from Sample
Does any one know what command(s) I can use from a SQL prompt to tell me what tables are cruuently residing in a particular database. I'm looking for something along the lines of "select list_of_all_tables from tempdb", which can be applied to any valid db on the system.
Hi, I got a request from a devloper asking, how can he find out what tables his stored procedure his using, so he can remove unwanted SP which are using old tables in the database.
Hello Everyone and thanks for your help in advance. I am working on an application that connects to SQL Server. I need to find out if there is any way (I know there is, not sure how) to retrieve a list of tables within a database and also, a way to retrieve a list of databases within a server. I am using VB.Net in a web application. Any help on this owuld be greatly appreciated.
I'm brain-dead today, sadly. If it weren't for IE remembering previous entries, I don't know if my name and email would have made it into the header correct :-)
I want the SQL command that lists the names of all user tables.
Alternatively, I have the following problematic Access 2000 code:
Public Sub ListAllTables() Dim cat As New ADOX.Catalog Dim tbl As New ADOX.Table Dim cnn As New ADODB.Connection Dim i As Integer, j As Integer Dim vgRet As Variant Dim intPrefixLen As Integer Dim strAppend As String
For j = (cat.Tables.Count - 1) To 0 Step -1 Set tbl = cat.Tables(j) With tbl Debug.Print tbl.Name vgRet = SysCmd(acSysCmdSetStatus, tbl.Name) End With Next
Set tbl = Nothing Set cnn = Nothing Set cat = Nothing DoCmd.Hourglass False vgRet = SysCmd(acSysCmdSetStatus, " ") End Sub
This code runs fine against an MDB but against SQL it includes all the views, rather than just the tables. If you have a fix for this, that will do just fine!