What SQL statement can i run to return a list of new tables or columns that exist in the Sales Database on Server A, which do not exist in the Sales Database in Server B.
Server A and Server B are linked servers.
I am using SQL Server 2005. Thanks.
I want to return a list of user tables from a database where the rowcount is 0. This will be a 3 step process: (1) truncate all 'New%' tables, (2) load data via ODBC/DTS into 'New%' tables, (3) list all 'New%' tables with zero rows (i.e. those that didn't get loaded, as all tables in the ODBC data source contain data).
I've tried:
select left(s2.name,32) as TableName, max(s1.rows) as Records
from sysindexes s1
inner join sysobjects s2 on s1.id=s2.id
where type = 'U' and s2.name like 'New%'
group by s2.name
HAVING max(rows) = 0
ORDER BY TableName
but of course there are multiple rows in sysindexes and the routine does not reliably return the correct list; for example the data in sysobjects & sysindexes, without the max and group by, might look like:
TableNameRows
NewARTxn0
NewARTxn1214800
NewARTxn1214800
NewARTxn1214800
NewARTxn1214800
NewARTxn1214800
NewARTxn1214800
I was hoping to come up with a single T-SQL statement that I could use in an xp_sendmail situation to email me the results.
Thanks for the suggestions.
Al
Hi everyone.
I am stuck on this for quite a while. Lets say i have 2 tables.
Table 1 with these columns:
Serial_Num
Product
Price
Table 2 with this column only:
Product
I need to create a VIEW that will show me all the the data in Table 1 but only for the column that exists in table 2. Example:
Something like this:
select (all the columns from table 2)
from table 1
I need it to be dynamic because columns could be added to both tables anytime.
Thanks.
how do I get value from column using list of tables?
For example, I have list:
schema_name, table_name, column_name
How do I get:
schema_name, table_name, column_name, column_value
Hi All.
I'm an Oracle DBA who's currently being asked to look at a SqlServer Database. I need a list of columns per table, but am having trouble.
I'll admit I might be being lazy here, but I'm in a hurry and using the valueable resources available to me!! Would really appreciate the sql i need to copy into the query window. Much obliged!!
I need........
Table A
Column1 Datatype
Column2 Datatype
Table B
Column1 Datatype
Column2 Datatype
etc....
Many thanks.
The following returns all base tables within the database of type "varchar":
Code:
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM mydb.information_schema.columns
WHERE TABLE_SCHEMA = 'master' AND TABLE_CATALOG = 'mydb'
AND DATA_TYPE IN('varchar')"
AND TABLE_NAME IN(
SELECT TABLE_NAME FROM mydb.information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'mydb' AND TABLE_SCHEMA = 'master')
What I then want to do is... For each of these results:
Code:
select [COLUMN_NAME] from [TABLE_SCHEMA].[TABLE_NAME]
WHERE ID = 'test'
Is it possible to do this in one SQL command? Or do I manually have to do it for each in the list from my first query?
Hey 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 RelatedIs there a way using MS SQL Server and Enterprise Manager to get a textdocument (or perhaps even a Word document) listing all table names,column names, etc of a database?--Sugapablo------------------------------------http://www.sugapablo.com <--musichttp://www.sugapablo.net <--personal
View 2 Replies View RelatedHere is a requirement. Need to update the columns in the tables with the latest values available in CSV.
The file is based on department so the list of tables which is under this department all the corresponding tables needs to updated.
The CSV file which is dynamic in nature the number of columns changes it has header of the columns that needs to be updated.
The destination tables are listed under department table for each department. So I have to update the columns in the tables with the values in csv.
Hello.
I need some help constructing a query i need to run on my database. I need to add 2 fields to every table in my databse. However, some of the tables already have1 or both these fields so i need to somehow do a check if the dield already exists. If it does not create the fields. Im using a MS SQL express 2005 server.
Could anyone help me construct this. Im pretty novice at SQL.
Thanks.
In SSMS, I connect Object Explorer to a partially contained database using a contained user login with password. This user has a database role of dbdatareader. When I try to expand the Tables in the database, I get the error:
The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Is there a way to set permissions for the contained user so that this could be done?
Hello
How I get the list of tables in a database. I'm using sql server 2000.
Thanks
Is there a query to get the list of tables in a database in sqlserver2000 and 2005?
View 1 Replies View RelatedHello,
I'm just wondering if anyone here knows how I can get a list of tables in reference to my database, Customers.mdf. This is SQL Server 2005 database.
I have tried using:
SELECT name FROM sys.sysobjects WHERE (xtype = 'U')
and, is listing system objects such as sysdiagrams. I would like to get tables in Customers.mdf only. Thanks
Can some one please tell me whats a best way ot get a list of all the tables that are present in a particular database.
Thanks
I'm running SQL Server 2012 Express. I need to query a database and return the list of tables in the database. How to construct this query?
View 4 Replies View Related
Hi
In SQL Server 2000 & 2005
How to Display list of Tables & S.Ps & Views in a Database?
Please advise
Thanks
I have tried SqlConnection.getSchema("Tables"). it returns me a list of all tables while getSchema("Views") returns me the views in the form of DataTable. But can we have a way to get them together in the same DataTable as a return result? because I want to read the rows by DataTable.CreateDataReader() later.
View 2 Replies View Related
How do I return a list of all user tables in a database where table name starts with EmpID_
for example the table names are:
EmpID_Countries
EmpID_Regions
EmpID_States
EmpID_Colorado
EmpID_Arizona
etc etc...........
I am using SQL Server 2005. Thanks.
I need a script that returns the last identity value generated for each table in a database that has an identity column.
I am using SQL Server 2005.
Thanks.
When using Sql Server Enterprise Manager and viewing a Database / Tables section, most of the tables if not all have a create date of 11/5/2004.Except for one, DNN_Users, has a creation date of 7/10/2007What factor could have caused that create date to have changed?What factors go into the date being set on that column in the database design? Does the date get updated say if I were go go in and change a datatype in a table?
View 5 Replies View RelatedHow to find out the list of all tables and views owned by a particular user in any database by querying the system tables?Thanks.
View 2 Replies View RelatedDoes SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.
Thank you very much.
Hi friends,I need to select columns for specific data from all tables in DataBasePls give me reply asap Regards,M.Raj
View 4 Replies View Relatedhow to find all primary key columns & foreign key columns in all database tables?
View 1 Replies View RelatedI have two tables:
1. tblProducts
columns: ProductID, ProductName
2. tblProductCodes
columns: ProductID, CustomerID, ProductCode
The 2nd table is for storing product codes for customers, in other words, one product can have different ProductCode for different customers. But some customers do not have ProductCode for a ProductID.
I want to create a query to return all the Products and its ProductCode (null is valid) for a specific customer.
I tried:
SELECT dbo.tblProductCodes.ProductCode, dbo.tblProductCodes.CustomerID,
dbo.tblProducts.ProductName,
dbo.tblProducts.ProductID
FROM dbo.tblProducts LEFT OUTER JOIN
dbo.tblProductCodes ON dbo.tblProducts.ProductID = dbo.tblProductCodes.ProductID
WHERE dbo.tblProductCodes.CustomerID = 2
But the query left out all products that does not have ProductCode value in tblProductCodes table for CustomerID = 2. I want all the ProductName returned from query and render null or empty string for ProductCode value if the code does not exist in tblProductCodes table for the customer.
Any help is highly appreciated.
I have table Products and Orders that has the following columns:
table Products: ProductID, ProductName
table Orders: OrderID, ProductID, OrderDate, Quantity, Price
The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.
I tried the following select statement:
select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'
Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :
ProductName / Quantity / Price
Product-A 5 1.89
Product-B 6 2.43
Product-C null null
Obviously, my sql statement won't work becaue the where clause will filter out Product-C.
Could anyone help me figure out how to modify my sql code to get the resultset I want?
Thanks in advance.
Hi all,
I am writing a portion of an app that is of intensely high online eCommerce usage. I have a question about identity columns and locking or not.
What I am doing is, I have two tables (normalized), one is OrderDemographics(firstname,lastname,ccum,etc) the other is OrderItems. I have the primary key of OrderDemographics as a column called 'ID' (an Identity Integer that is incrementing). In the OrderItems table, the 'OrderID' column is a foreign key to the OrderDemographics Primary Key column 'ID'.
What I have previously done is to insert the demographics into OrderDemographics, then do a 'select top 1 ID from OrderDemographics order by ID DESC' to get that last ID, since you can't tell what it is until you add another row....
The problem is, there's up to 20,000 users/sessions at once and there is a possiblity that in the fraction of a second it takes to select back that ID integer and use it for the initial OrderItems row, some other user might have clicked 'order' a fraction of a second after the first user and created another row in OrderDemographics, thus incrementing the ID column and throwing all the items that Customer #1 orders into Customer #2's order....
How do I lock a SQL table or lock the Application in .NET to handle this problem and keep it from occurring?
Thanks, appreciate it.
I have a string which I need to know where it came from in a database.I don't want to spend time coding this so is there a ready made scriptwhich takes a string as a parameter and searches all the tables whichcontain varchar type columns and searches these columns and indicate whichtables contain that string?Full text search is not enabled.--Tonyhttp://dotNet-Hosting.com - Super low $4.75/month.Single all inclusive features plan with MS SQL Server, MySQL 5, ASP.NET,PHP 5 & webmail support.
View 1 Replies View RelatedI am working with several similar tables that for the most part contain the same columns. I need to export these tables to different csv files, and the csv file needs to have all possible column names and blank fields if the column does not exist in the table. Basically a set and subset thing.
for example
TABLE AA has three columns A, B, C
and
TABLE AB has 2 columns B, D
I am working with over 200 tables, and am planning on automating this with c#. I could use the information_schema to create the SQL statement for each tabel but it sems like I should also be able to do this using one select statement.
I want to write a select statement that combines this.
SELECT
[A],
[B],
[C],
'' AS [D]
FROM
[AA]
SELECT
'' AS [A],
[B],
'' AS [C],
[D]
FROM
[AB]
Hi.
A newbie question. I am tearing my hair out trying to work out how in Sql Server 2005 to get a printout (or even better a file I can save that i could incroporate in a wrod document), or both, which shows the structure of all the tables in my database.
I want to list all tables (or selected tables perhaps) , and all columns in those tables, with the attributes of each column (nvarchar(2) etc or decimal(18,5) etc). Just a simple listing of all tables and their columns and the attributes of those columns.
Surely this must be possible with a simple one click operation in Sql Server 2005. I have created a database diagram which gives me part of what I want, but that just shows the tables, relationships, and column names, not the attributes of each column which is what I need as well.
I don't want to have to start installing third party products to do this, and I have no great script writing capabilities. Surely such a basic function is easily acheivable with one or two clicks in Sql Server 2005 from a menu somewhere in sql server mangaement studio?
Thanks in Advance for your help.
Chris M
Hi,
I'm trying check the records which satisfies the following condn.
select @p1 = su1 from RER where (no=10 and atp ='N')
It returns empty list . bcos no match found.that's ok.
But i need to write the logic if record is not there.
i tried to do as follows
if ((@p1 =null) or (p1=' '))
...some logic
else
.. other logic
It's always executing else case. How do i catch empty value using sql query.
Is there any command there like null?
I tried in SQL query analyzer. Using SQL 2000.
Thank You
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'