How To Find Out Tables Which Can't Be Replicated
Jun 22, 2004Is there any query to find out all the tables without a Primary key or without a Unique index ?
View 3 Replies
ADVERTISEMENT
How To Find Out Wether A Row Has Been Replicated?
Jan 20, 2007
Dear ppl,
In Merge Replication SQL Server2005, what is the easiest way of finding out wether a row on the publisher database has ever been replicated to subscribers?
Regards
Transact SQL :: Find How Many Rows Replicated
Aug 20, 2015How Do we find how many ROWS REPLICATED to subscriber at specific interval ?
View 3 Replies View RelatedChanging Indexes In Replicated Tables
Dec 19, 2001I need to drop and recreate indexes in some of my tables that are currently been replicated. I am not sure how this will affect my ongoing replication. Will this cause a problem for me? Please help
Bright
Replicated Readonly Db And Temporary Tables
Mar 20, 2007I have two replicated databases i.e.
1. Database 1 is a live database where all live applications point
2. Database 2 is a replica of Database 1 and all reporting / BI applications point to this db. This is read-only.
Is it possible for reporting applications, pointing to Database 2, to create temporary tables in the read-only database?
Thanks
Changing Table Props On Replicated Tables
Dec 13, 2001How can I change a field size that is set to numerical 9,2 to 9,3? I need to allow 3 decimal places.
Thank you,
Kameron
A Deadlock When Selecting From A View With Replicated Tables.
Dec 15, 2006I moved this from another forum because it seems more related to replication the longer I look into it!
The following code often dies on a deadlock. I have also seen "Spid x blocked by Spid x", where x is the spid of this connection. The view selects from several tables and some other views as well, and many of the underlying tables are being updated by replication. I have seen cases where the replication Insert proc participated in the deadlock, and the table being inserted into is joined twice in the view, suggesting that somehow this
causes the deadlock or makes a deadlock more likely?
SELECT * INTO dbo.tbl_stg_LoansMarketingFirstBorrower
FROM view_loans_marketing_first_borrower OPTION (MAXDOP 1)
I do not understand how selecting from a view can cause a deadlock with a
proc which does not reference tbl_stg_LoansMarketingFirstBorrower. Any
suggestions on how to diagnose this? I used (1204) to identify the proc and underlying table.
Thanks a bunch.
SQL 2012 :: 2 Replicated Objects (tables) Not Being Created On Subscriber
Sep 10, 2015I'm in the process of migrating over nearly 900 reports to a replicated server.
I have moved over 100 reports, stored procedures and their dependent objects so far.
I have two tables that are not being applied to the subscriber.
Ug! Replicated Tables Missing Default Values And Identities.
Feb 8, 2007Ok, so I must have screwed something up.
I have several databases set up for transactional replication to another instance of SQL Server 2005 for fail over purposes. Today, I restored one of those replicated databases to my development machine and discovered two surprising problems:
1) The Default Values settings in the replicated tables are missing. They are there in the publishing tables, just as they were before I set up replication. However, they are not in the subscribing tables. Now, this is not such a big issue, since I tend to send all default values in insert queries as necessary.
2) The second problem is a more of an issue, since I use auto-numbered Identity columns in my tables (yes, I know that's just plain lazy...). Anyway, in the replicated tables, €śIs Identity€? is indeed set to yes, but despite that fact that there are thousands of records with incrementally unique IDs, SQL server is trying to insert a record starting with 1. This, of course, throws a PK constraint error.
Obviously, if I am use them for failover purposes, these replicated databases need to be identical in every way.
So, what did I do to cause this situation, and how to I fix it?
Thanks a bunch!
md
Anything That You Find In SQL Object Scripts, You Can Also Find Them In System Tables?
Jul 26, 2005I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
View 4 Replies View RelatedFind A Key In All Tables
Oct 28, 2013I have many tables that have as one of its keys POLICY_NUMBER is there a way to write a script that will look in all tables in a db for a certain POLICY_NUMBER
View 5 Replies View RelatedFind Tables With No PK
Jan 27, 2004I modified my origianl script (used a cursor) to use a looping method I saw in a different thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30286 by Nigel Rivett. Since I used code on this board I though I would post back for the greater SQL Team script toolbox. Feel free to use it to catch those deliquent sql programmers who forget to create a PK. Enjoy.
declare @database varchar(128) ,
@maxdatabase varchar(128) ,
@cmd nvarchar(1000)
create table #pk (databasename sysname, tablename sysname)
select @database = '', @maxdatabase = max(name) from master.dbo.sysdatabases where dbid > 5
while @database < @maxdatabase
begin
select @database = min(name) from master.dbo.sysdatabases where dbid > 5 and name > @database
set @cmd = 'select ''' + @database + ''', o.name from ' + @database + '.dbo.sysobjects o join ' + @database + '.dbo.sysindexes i on o.id=i.id
where o.xtype=''U'' and i.indid = 0'
insert into #pk exec sp_executesql @cmd
end
select * from #pk
drop table #pk
- Eric
Find Available Tables In A DB
Mar 20, 2008I am brand new to using SQL. I created a DB with a bunch of tables there. What I would like to do is from my VB.NET application I would like to allow the user to see what tables are available in that DB.
Also I need a way to check if a table exists. I used the following:
strTemp = "IF EXISTS " & _
"(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'simplesql') AND type in (N'U'))"
cmd.Connection = conncmd.CommandType = CommandType.Textcmdcmd.ExecuteNonQuery()
but I get an exception : Incorrect syntax near ')'
thanks for your help
How To Find All The Tables Which Are In Database
Feb 24, 2008Hi......i want to find all the tables which are in database. is there any hint or code that can help meThanks in advance
View 4 Replies View RelatedFind Tables With Column Name
Oct 16, 2006How can I find all the tables with a specific column name?
View 3 Replies View RelatedFind Same Coumn In Other Tables
Jun 9, 2008Is there any way i can find some certain fields in the whole database? like i know a column called Type_ID in table Sys_Type, how can i know there are same Type_ID column in other tables? Thanks in advance.
View 2 Replies View RelatedFind All Differences Between 2 Tables
Mar 5, 2015I have this 2 tables:
Table1: Table2:
ID Value ID Value
--------- ---------
1 2 2 1
2 1 3 4
4 3 4 4I
need to find all differences between the 2 Tables.
A mismatch can be:
- Table1.ID exists, Table2.ID does not exist
- Table2.ID exists, Table1.ID does not exist
- Table1.ID = Table2.ID and Table1.Value <> Table2.Value
So I want to be returned:
ID Value ID Value
--------------------
1 2 NULL NULL
4 3 4 4
NULL NULL 3 4
There must be something simpler than the solution I came up with which is:select * from Table1 a full outer join Table2 b on a.Id = b.Id
WHERE a.value <> b.value or (a.value IS NULL and b.value IS NOT NULL) or (a.value IS NOT NULL and b.value IS NULL)Martin
How To Find The Tables In A View
Nov 15, 2006Dear friends,
is there any way to find the table names from a view?
ex: suppose i have a view named as vorganization.i need all the tables in this view. is it possible to get through a query? same case for procedures and functions also.........
or we have to use sp_helptext?
is there any better way?
thank you very much.
Vinod
How To Find No. Of Databases And Tables?
Mar 26, 2008Hi,
is there any way to find out the no. of databases and
no. of tables in a particular database using a sql query ???
If any server variable is used then please mention that???
thanx in advance
San
How Can I Find Connection Between Two Tables???
Mar 19, 2008Hi,
I have a database with 150 tables.
I need to find connections between specified tables. (I need to find through what fileds and tables are table1 and table2 connected)
Is there any tool in SQL Server for this?
Thanx
How To Compare Two Tables And Find The Gap?
Mar 18, 2008
Hi,
We have inventory table which is updated everyday.
I need to find the gap between updated table vs history (which means yesterday's data).
what i did is I just copy the inventory everyday before it is updated.
So, whenver inventory table is updated I want to know the gap between these two (history vs updated) tables.
The structure of two tables are same and here are columns:
date_key (fk)
store_key(fk)
item_key(fk)
begin_inventory_dollars
ending_inventory_dollars
begin_inventory_units
ending_inventory_units
I want to compare each row and if the value is changed I want to have a gap (updated value - history value) and if data is new then just want to add.
Here is tsql: however when I compare to gap between table from excel spreadsheet and this query, this query does not return a right value. (Some are correct but some don't)
Code Snippet
select a.date_date_key,
sum(a.Beg_Inventory_Dollars- (case when b.beg_inventory_dollars is NULL then 0 else b.Beg_Inventory_Dollars end)),
sum(a.Beg_Inventory_Units-(case when b.beg_Inventory_Units is NULL then 0 else b.beg_Inventory_Units end)) ,
sum(a.Ending_Inventory_Dollars- (case when b.Ending_Inventory_Dollars is NULL then 0 else b.Ending_Inventory_Dollars end)),
Sum(a.Ending_Inventory_Units-(case when b.Ending_Inventory_units is NULL then 0 else b.Ending_Inventory_Units end))
from UPDATED TABLE a
left outer join HISTORY TABLE b
on b.item_key = a.item_key and
b.store_key =a.store_key and
b.date_key =a.date_key
group by a.fisc_date_key
Did I miss something in here?
Any kind of advice would be grealy appreciated.
Thanks.
Find Max Serial Number From 2 Tables
Feb 11, 2008I need a query to find max serial number by comparing two different tables. Here is my requirementI am having two tables named Table1 and Table2. Each tables having more than 30,000,000 records.I want a simple query to find Max srno from two tables.For exampleIf Table1 max is 245 where partno=2 and ano=2and Table2 max is 343 where partno=2 and ano=2Then 343 is max serial noIf Table1 max is 435 where partno=2 and ano=2and Table2 max is 34 where partno=2 and ano=2Then 435 is max serial noI used this query but its taking more time select max(v.MaxSrNo) from ((select max(MaxSrNo) as MaxSrNo from Table1 where partno=@partno and ano=@ano)union all (select max(MaxSrNo) from Table2 where partno=@partno and ano=@ano)) as v Pls give me a simple query to find max srno.
View 3 Replies View RelatedHow To Find Text/data In All Of My Tables?
Jan 18, 2000Hello,
I have a quick question. Can you please tell me how to find/search a string/value in all of my tables in a database.
For example I need to find a value/data "GH45678", where-ever it appears in a database.
I would really appreciate your prompt response. I would also like to thank you very much in advance for your time/help.
Note: I need to find specific data in all of my tables, I am looking for something like text search in my database. I am using SQL Server 6.5. Thanks
How To Find Out All The Statistics From All The Tables And Drop Them
Jul 21, 2003How to find out all the statistics from all the tables and drop them..any script anyone can help with?
When we are trying to make datatype changes in few related tables,it's giving error saying that some statistics are dependent onthe column blah blah...
Thanks,
Sheila.
How To Find Maximum Number From Two Tables
Feb 19, 2012My db contains two table Employee_detail and Student_Detail.Employee_detail has Emp_id and Student_Detail has stud_id.Now my problem is that i want to get one maximum no from these two table.
View 11 Replies View RelatedCompare Two Tables And Find The Occurance
May 17, 2008Hi friends,
I have a two table with following fields, table names are tbl_userinfo, tbl_Property.
tbl_userinfo fields are
user_id name
1 dhin
2 Mike
3 sam
4 Red
tbl_Property fields are
prpty_Id User_Id Address
1 1 3CostalRoad
2 1 westbengal
3 2 Loasass
what i want to do is, if tbl_info User_id occures in tbl_property, i want to display that full info abt tbl_userinfo
after comparing two tables Expected result is
user_id name
1 dhin
2 Mike
Please help me how to do this
Find The List Of Tables For A User?
Feb 18, 2015how to find the names of the tables owned by the particular user in sql server and how to display the distinct object types owned by the particular user.
View 1 Replies View RelatedWhere To Find Info On The Concept Of SQL Tables
Feb 7, 2006i would like to know where can i find information on the concept of SQL like Base Table , View Table and Search Table as now i am using VS.Net 2005 and SQL Server 2005 to write a addressbook program and i am just a newbie to Sql and c#... i wish to write my sql commands in c# side before passing it into sql and may i know how to do it?
View 20 Replies View RelatedFind Common Value In Column For 30 Tables
Mar 21, 2006Hi,
I am trying to query for a common value in a column called "file_auth_nbr" in 30 different tables. I was going to try something like this (see below) but wasn't sure if this was the most efficient, fastest, or correct, way to get what I'm looking for:
Select distinct a.file_auth_nbr from table1 as a
join table2 as b
on a.file_auth_nbr = b.file_auth_nbr
join table3 as c
on a.file_auth_nbr = c.file_auth_nbr
join table4 as d
on a.file_auth_nbr = d.file_auth_nbr
join table5 as e
on a.file_auth_nbr = e.file_auth_nbr
......etc., etc.
Any suggestions would be much appreciated,
Jeff
How Can I Find What Tables Are Loaded In Memory ?
Jul 23, 2005I'm doing some performance reviews and wish to know what tables SQL haspinned in memory and which ones have are loaded through usage ...Is there a way ?Thanks,Craig
View 3 Replies View RelatedFind Similar Strings In Two Tables
Jul 23, 2005I have two tables in the same SQL database. Both have a similar numericfield. One field has the same number as the other field but is prefixedwith various letters. I know how to use LIKE but won't know the partialstring I am looking for. I am trying to use LIKE '%' + Field A orsomething that will do this.Eg.Table 1 Field A is 'A12345"Table 2 Field B is '12345"I want to find every record in Table 1 Field A that contains the exactField B data.*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies View RelatedFind All Tables That A Table Is Related To
Sep 8, 2006how can i find all the names of tables that a specific table is related to in tsql?
View 3 Replies View RelatedHow To Find The Last Updated Tables In A Database
May 28, 2007Hi,
I am trying to create a data dictionary for a huge application which has aroung 300 tables in the database....when i perform any operation in the application some tables are updated.... can you help me to find out how can we find out the last updated tables in the database ??