Changed Table Structure

Mar 1, 2004

Hi all,

How can i get the information as to when was the last my particular table structure was changed (for ex. adding of a column/dropping a column)

Pls let me know
TIA

View 3 Replies


ADVERTISEMENT

Transact SQL :: Dynamically Alter Schema Of Stage Table When Source Table Structure Changed?

Oct 25, 2015

we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.

View 4 Replies View Related

Cache Database Structure (How To Detect If Database-design Has Changed..)

Feb 24, 2006

Hello everyone,I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.I want to know if a column or table has changed any values, like datatype, name, properties, etc.Any suggestions out there ?!

View 3 Replies View Related

SQL Server 2012 :: Compare Two Table Data And Insert Changed Field To Third Table

Aug 12, 2014

I want Compare two Table data and insert changed field to the third table ...

View 9 Replies View Related

Transact SQL :: Update Table From Linked Table If Something Has Changed?

May 20, 2015

Is it possible to check/notify if something (record) has changed in the linked table (from linked server) and automatically update table in local one?

View 4 Replies View Related

Can Table Ownership Be Changed?

Jul 20, 2005

When I create tables in SQL I can specify dbo as the owner using thesyntax below but when using the upsize wizard in Access 2000 I owneverything. Is there a way that the system administrator can changeownership after the fact?CREATE TABLE dbo.mytable (c1 int not null)Thank You,Randy KJoin Bytes!

View 1 Replies View Related

How To Put Only Changed Fields Into A New Table

Mar 6, 2008

Ha all again.
I have this issue:
I have 2 tables:
t1 (id,field1,field2,field3)
and t2 (id,field_name,field_value)

is possible with trigger to insert only changed fields on t1 to t2 like

(changing field1 on t1 with id 21 cause a new row in t2 with fields id=21,field_name="field1", field_value=field1.value)

i hope you understand what i look for.

Is this trigger solution ?

CREATE TRIGGER MAP_CHANGESON t1AFTER INSERT,UPDATEBEGIN
DECLARE @ID SOMEDATA;DECLARE @FIELD1 SOMEDATA;DECLARE @FIELD2 SOMEDATA;DECLARE @FIELD3 SOMEDATA;SET @ID=(SELECT ID FROM INSERTED)SET @FIELD1=(SELECT FIELD1 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD1<>T2.FIELD1)SET @FIELD2=(SELECT FIELD2 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD2<>T2.FIELD2)SET @FIELD3=(SELECT FIELD1 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD3<>T2.FIELD3)IF @FIELD1<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD1','@FIELD1')IF @FIELD2<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD2','@FIELD2')IF @FIELD3<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD1','@FIELD3')RETURNEND

View 7 Replies View Related

Database Automatically Creates Xxx_Temp Table While Modifying / Updating Table Structure .

Dec 16, 2007

Hello friends,

I am new to the SQL Server 2005 development.

From last 1 week or so, i have been facing very strange problem with my sql server 2005s database
which is configured and set on the hosting web server. Right now for managing my sql server 2005 database,
i am using an web based Control Panel developed by my hosting company.

Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database,
it gives me error saying that,

"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors.
Source: .Net SqlClient Data Provider".

where xxx is the table name.

I have done quite a bit research on the problem and have also searched on the net for solution but still
the problem persist.

Thanks in advance. Any help will be appreciated.

View 5 Replies View Related

Transact SQL :: Table Structure - Inserting Data From Other Temp Table

Aug 14, 2015

Below is my table structure. And I am inserting data from other temp table.

CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,

[Code] ....

Now in a next step I am deleting the records from #revf table. Please see the delete code below

DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2

[Code] ...

I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:

SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],

[Code] ....

If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.

View 5 Replies View Related

How To Check A Table Whether Data Is Changed Or Not..??

Jan 30, 2006

Hi..

I got 10 Tables with data in it for 100 Loans. The data can be Good and Bad .....

I had a Update Proc which Updates the 10 tables with the Good data what ever i pass...for this 100 Loans
Tha Proc will update the existing data in all tables whether it is Good or Bad Data.

when the updating is completed I want to know what are the Loans that were updated where there is a Bad Data in atleast one Field of the 10tables.

I don't want to check field by field in each table.... if there is a bad data and my proc updated with a Good Data i want to know that Loan.

Can any one has an idea on this,,,,

Thanks
Bob

View 2 Replies View Related

How To Find Out If A Spesific Table Has Changed?

Feb 28, 2008

Hi,

I have a small question regard how to find out if a specific table has chanced.
Is there any (sql)function inside MSSQL2005 that I can use to find if a spesific table has changed? I have heard of timestamp, but I don't know if that will solve my problem? Is there any thing in the sys.tables that kan help me, just do a fast query and check if a table has changed?

I'm developing a web-application with AJAX.NET, and I have UpdatePanels with datalist. When the tables in the db gets big the application is henging for a sec or two when it receiving the data, and when the timer (refresh rate) is 2 min it is not good! So my plan is to check if the table has chanced since last transfer of data, and if it has it will download the data, if not do nothing.

Jonny

View 4 Replies View Related

Subscriber Pull- Hows It Know Table Changed.

Jan 7, 2000

When you setup a subscriber to a publication, that is pull the publication, does it copy all the rows or does it keep track of which rows changed.

View 1 Replies View Related

T-SQL (SS2K8) :: Get Recent Changed Value From History Table

Apr 6, 2015

I have a history table with the following values

CREATE TABLE History (SnapShotDate DATETIME, UID VARCHAR(10), DUEDATE DATETIME)

INSERT INTO History VALUES ('03-23-2015','PT-01','2015-04-22')
INSERT INTO History VALUES ('03-30-2015','PT-01','2015-04-20')
INSERT INTO History VALUES ('04-06-2015','PT-01','2015-06-30')

[Code] ....

I need an output in the below format. I need the most recent changed value for any given UID. Need to get the below result

OUTPUT
UID PreviousDueDate CurrentDueDate
----------------------------------------
PT-01 2015-04-20 2015-06-30
PT-02 2015-04-22 2015-04-22
PT-03 2015-04-18 2015-04-22
PT-042015-04-222015-04-18

View 4 Replies View Related

Table Schema Changed At Runtime In Cursor

Apr 8, 2008

none

View 3 Replies View Related

Column Compare In Same Table And Show Only Changed Value

Aug 18, 2014

How can I compare column in the same table and show only column and value that has been changed.

declare @t table (GroupID CHAR(6),Text1 VARCHAR(MAX),Text2 VARCHAR(MAX),Text3 VARCHAR(MAX))

insert into @t
SELECT '11111','Text1','Text2','Text3'

insert into @t
SELECT '11111','Text1','Text2','Text4'

END RESULTS:

Column Name Old New
Column 3 |'Text3' |'Text4'

View 9 Replies View Related

Find The Last Time The Data In A Particular Table Was Changed

Mar 27, 2008



Is there a Tool/Utility/StoredProcedure/Query/View that can tell me the last time the data in a particular table was changed?

That change can be the result of either an ADD , INSERT, or DELETE statement.

I am using SQL Server 2005.

View 3 Replies View Related

Auditing:Extracting Changed Fields From Inserted Table

Jan 15, 2007

Hello,I'm creating an audit table and associated triggers to be able to captureany updates and deletes from various tables in the database. I know how tocapture the records that have been updated or deleted, but is there any waythat I can cycle through a changed record, look at the old vs new values andcapture only the values that have changed?To give you a better idea of what I'm trying to do, instead of creating acopy of the original table (some tables have many fields) and creating awhole record if a type or bit field has been changed, I'd like to onlycapture the change in a single audit table that will have the followingfields;AuditID int INDENTITY(1,1)TableName varchar(100)FieldName varchar(100)OldValue varchar(255)NewValue varchar(255)AuditDate datetime DEFAULT(GetDate())Any direction would be greatly appreciated.Thanks!Rick

View 6 Replies View Related

Can A Column Data Type Be Changed On A Replicated Table?

Dec 21, 2006

On sqlserver 2000 transactional replication:

How would I best go about changing a published table's column from smallint to int? I could not find anything about it in BOL or MS.com. I do not think EM/Replication Properties allows the change. I suspect I have to run "Alter Table/Column" on the Publisher and each Subscriber the old-fashioned way. Is that true?



Thanks!

View 3 Replies View Related

Could Not Complete Cursor Operation Because The Table Schema Changed

May 30, 2006

Microsoft SQL Server  2000 - 8.00.2039

Got this error:

Could not complete cursor operation because the table schema changed after the cursor was declared. SQLCode: 16943 SQLState: HY000

Is this a known issue?  I suspect the application logic may cause this error. Please advise.

Thanks a lot!

 

View 16 Replies View Related

Copy Records From One Table To Another Table With Same Structure

Jan 20, 2007

Hi allI have two tables in SqlServer with Exactly Same Structure,I want to Copy all Records fromone of them to another one.I came across to "Insert....select..." statement But i have two problem
1) I don't know any thing about Columns name!!! i just know they have same structure and as far as i know , "Insert...select..." need the Column list to operate correctly, am i right?
2) these two table have One Prinary Key column with IDENTITY feature.
Any Help Greatly appriciated.Regards.

View 6 Replies View Related

SQLSERVER 2000,How Can I Detect The Last Time Where The Table Has Changed (updated)?

Jan 2, 2008

Hi, I wonder where I can see last update over a table. Maybe sqlserver write some information about every table.

For example there are 300 tables in a database an I want to execute some query to see the last tables updated (list of tables updated > 2008-01-02 )

Hope to be clear,
Best regards
Ariel

View 4 Replies View Related

What And Why Is Better Table Structure?

Mar 7, 2008

Structure Number1 
Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate
Structure Number2 
Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryNativeId(P.K.), CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate
Can anyone tell me that between above 2 structure what is better and why?
I just add CategoryNativeId(P.K.) in Number 2 structure.

View 2 Replies View Related

Table Structure

Mar 17, 2004

Dear all,

how can I use a store procedure to get the structure of a table like data type, length, description etc.

I have used sp_columns to get the information but it can't get the description of each field.

Thanks.

View 1 Replies View Related

Table Structure

Jul 13, 2004

How can I get the structure of a table?
With MySql I can do it this way: DESC | DESCRIBE <tablename>

Thank you,

Dirk
Dirk.Ulrich@gmx.de

View 5 Replies View Related

I Can Really Use Some Help.Table Structure

May 4, 2004

How can I setup a table structure for the diagram shown in the attached bitmap?

1) I need to create a product using labour and materials.
2) I need to create hardware using labour and materials.
3) I need to be able to include some hardware in some products.
4) Some materials in the product are made of a culmination of materials. E.G., Concrete is made of sand, stone, and cement.

Any suggestions?

So far I have :


USE NORTHWIND

Create Table tbProducts (
ProductID int NOT NULL,
Product varchar(50)
)

go

ALTER TABLE tbProducts
ADD CONSTRAINT tbProducts_pk PRIMARY KEY (ProductID)
GO

CREATE Table tbMaterials (
MaterialID int NOT NULL,
Material varchar (50)
)

GO

ALTER TABLE tbMaterials
ADD CONSTRAINT tbMaterials_pk PRIMARY KEY (MaterialID)
GO

CREATE Table tbLabour (
LabourCode char (2) NOT NULL,
Labour varchar(50)
)

GO
ALTER TABLE tbLabour
ADD CONSTRAINT tbLabour_pk PRIMARY KEY (LabourCode)
GO

CREATE Table tbProductMaterials (
fkProductID int NOT NULL,
fkMaterialID int NOT NULL,
Quantity Float NOT NULL
)

GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterials_pk PRIMARY KEY (fkProductID, fkMaterialID)
GO

ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterils_fk FOREIGN KEY (fkMaterialID)
REFERENCES tbMaterials (MaterialID)

GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterils_Product_fk FOREIGN KEY (fkProductID)
REFERENCES tbProducts (ProductID)
GO

CREATE TABLE tbProductLabour (
fkProductID int NOT NULL,
fkLabourCode char(2) NOT NULL,
Manpower int NULL DEFAULT(0),
Hours float NULL DEFAULT(0.0)
)

GO

ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_pk PRIMARY KEY (fkProductID, fkLabourCode)
GO

ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_fk FOREIGN KEY (fkLabourCode)
REFERENCES tbLabour (LabourCode)

GO

ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_Product_fk FOREIGN KEY (fkProductID)
REFERENCES tbProducts (ProductID)

GO

CREATE TABLE tbHardware (
HardwareID int NOT NULL,
Hardware varchar(50)
)

go

ALTER TABLE tbHardware
ADD CONSTRAINT tbHardware_pk PRIMARY KEY (HardwareID)
GO


CREATE Table tbHardwareMaterials (
fkHardwareID int NOT NULL,
fkMaterialID int NOT NULL,
Quantity Float NOT NULL
)

GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_pk PRIMARY KEY (fkHardwareID, fkMaterialID)
GO

ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_fk FOREIGN KEY (fkMaterialID)
REFERENCES tbMaterials (MaterialID)

GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_Hardware_fk FOREIGN KEY (fkHardwareID)
REFERENCES tbHardware (HardwareID)
GO

CREATE TABLE tbHardwareLabour (
fkHardwareID int NOT NULL,
fkLabourCode char(2) NOT NULL,
Manpower int NULL DEFAULT(0),
Hours float NULL DEFAULT(0.0)
)

GO

ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_pk PRIMARY KEY (fkHardwareID, fkLabourCode)
GO

ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_fk FOREIGN KEY (fkLabourCode)
REFERENCES tbLabour (LabourCode)

GO

ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_Product_fk FOREIGN KEY (fkHardwareID)
REFERENCES tbHardware (HardwareID)
GO



This table structure does not include anything about accounts and item 4) making materials from 1 or more other materials (E.G., Concrete).

Any hints how to incorporate these outstanding items?

Mike B

View 3 Replies View Related

How 2 Get The Table Structure Using T-SQL

Sep 11, 2006

How can I get my table structure (columns, their types, whether it s an Identity column) using T-SQL
pls
Thanks you

View 5 Replies View Related

Table Structure

Mar 10, 2007

Hi

How to write a query for table structure which looks like
SQL Design table.


thanks

asm

View 15 Replies View Related

Structure Table ..

Dec 13, 2007

I'm new in creating tables. and I was asked to create table(s) to capture information using SQL server 2005 (express edition)

please read below and share your thoughts and opinions o what's the efficient and best way to structure the tables.

I need to capture user's input daily and every hour, text can be from 20-100 characters, and my company is expecting to have 50,000+users in the future...Also the hrs's text can be stored for max 120 days, after that data would be deleted from the table(s)
An example is put all together, (not sure if it is the right and efficient way)

An example is put all together, (not sure if it is the rigth and effectient way) and if database would support that.. and if it will be fast enougth to search for data

(table structure) - (example with 3 users)
userId | timestap | message
1 12/12/2007 10:00 some message
1 12/12/2007 11:00 some message
1 12/12/2007 12:00 some message
1 12/12/2007 13:00 some message
1 12/12/2007 14:00 some message
2 12/12/2007 10:00 some message
2 12/12/2007 11:00 some message
3 12/12/2007 12:00 some message
3 12/12/2007 13:00 some message
3 12/12/2007 14:00 some message ...etc..



Thank you..

View 11 Replies View Related

Table Structure

Feb 28, 2008

Hi Guys,

I have another question.

Is there a query to display the structure of a certain table?
e.g. display the columns, data type,...etc.

TIA

View 7 Replies View Related

Table Structure In XML

Jul 20, 2005

Hi,I am new to XML and need a structure of table in XML format, which needsto be created as a table in another server.The Source and destinationservers are SQL Server 2000. Could anyone help me on this.Balaji.S.--Posted via http://dbforums.com

View 1 Replies View Related

Best Structure For The Table

Feb 11, 2008



Hi,
I have one field "Status" with 15 different types of values. Like 1, 2, 3, 4 .. 15.
Now this Status field contains the values like this:
Id Status
1 1,2
2 3
3 1,2,3
4 13,15

What is the best way to organize this information in SQLServer table?
My idea is Create one table with the following volumns: ID, Status1, Status2, Status3, ... Status14, Status15.

Now the above data look like this:
Id Status1 Status2 Status3 ... Status13 Status14 Status15
1 1 1 0 0 0 0
2 0 0 1 0 0 0
3 1 1 1
4 0 0 0 1 0 1

So, In future If they add new column they can easily add column like status16.
And status table look like this
Status
StatusID Description
1 a
2 b
..
15

Thanks in advance

View 6 Replies View Related

Question On Table Structure

Jun 24, 2007

 All,On my aspx page i have 2 sets of checkboxlists, each with a number of items and i am storing that info in my db. currently i have my table set up this way: InfoID--PK autoUserID-intCheckBoxList1ID-intCheckBoxList2ID-int  So a user can select more than 1 item in each checkbox, so this table will contain multiple user-ids and each id from the respective checkboxes. My question is, is there a better way to store this data? I hope this is clear, any input would be greatly appreciated.Thank you.  

View 2 Replies View Related

Change The Table Structure

Aug 30, 2007

Hi....I execute select statement on three tables and i get the following table: 




ID


Value


Name




1


10


color




2


20


color




3


30


color




4


40


color

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved