Hi all expert,
Question as stated title above, how do I duplicate the table structure to another database whether it copies all columns, primary and foreign keys, default values, descriptions, constraints, indexes
Many thanks for the help.
regards,
We have databases with large numbers of tables. We have a separate database for each year. For various reasons, we need to export about 100 of the tables (Structure only, not their data) from last years database into this year's database. What is the best method for doing this? The import/export wizard creates the tables but does not bring in important things like keys.
Regards Shirley A
I have a SQL 7 database with about 30 tables in. For documentation purposes I need to print out the structures of each table. I have tried using the diagram tools but no joy.
I have access to Access, Crystal Reports, if these are any use?
Please help!!
Tony Kavanagh
I will soon be writting a script which will search threw all tables in my database and try to find how they may be linked to one another, and also which tables are called by which stored procedures, any suggestions or tips would be great.
View 1 Replies View RelatedHi all,
Could anyone let me know if there are any Scripts available for comparing the schema differences between two SQL server databases?
Thanks
DBAnalyst
Let us suppose that I have two similar databases and need to create ansql-script upgrating one database structure to another. For example, thesedatabases are from different versions of some software, first is from earlyversion, next is from current, and second one contains several new tables,sevelal new fields in old tables, several new or changed stored procedures,UDFs and so on.How to solve this problem using standard tools?
View 6 Replies View RelatedHi
I have a problem out here. I have two databases with the same table structure
and I want to merge data from both the tables . Please can anyone let me know.
Iam using Sql Server 7.0.
Thanks
Bye
Hello !! :O)
I have a SQL 7 database with some tables in. For documentation purposes I need to print out the structures of each table and all the rows in every table.
Please help!!
Thanks
CREATE TABLE table_name AS SELECT STATEMENT WHERE rownum = 0; this query will copy the table structure of one table without copying the data. I need to copy the table structures of all the tables in a particular database without copying the data.suggest me on this.
View 6 Replies View RelatedI need to get a print out of column names, types, and properties for each of the tables. We just started using SQL Express from Access. I cannot find any reference to printing the structures in Microsoft SQL Server Management Studio Express.
Thanks
I am having a hard time finding materials on this subject. I am guessing I am using the wrong keywords to search. Basically, I want to be able to modify database tables through a web form. They can add columns and delete columns through the form. I would just want to default the type of column and the length. I am sure it has been done, I was just wondering if anyone had some resources they could throw my way. I would appreciate it. Thanks.
View 1 Replies View RelatedHi.
Is there anyway to export the table structures : data type,length,NULLABLE,Description into an Excel file using MS SQL Server?
Or I need to do it manually?
Thank you in advanced.
Sincerely
Agustina
Hello Everyone:
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
How do I fix this?
TIA
Hi,In my program i need to duplicate a table in a current data base.I'm thinkin' of reading the data base columns and then rows and so icreate another tableIs there any other easy and fast method with SQL Server 2005, becausemy idea is so slowI'm using VB 2005 Express with SQL Server 2005 ExpressThanks
View 2 Replies View RelatedHi,
I have a table with data in it, (tblT1)
A. How do I create a table tblT2 with the same columns in tblT1, but without data in (blank tblT2 table with columns).
B. And also how do I copy the enitre table's data (tblT1) into tblT2.
Thank you,
I have 2 test database identical in size and table structure, only one resides on filegroups and one resides on a single file.
From everything I have heard filegroups are suppose to improve performance. I stop and start the server before each test to clean out
cache. When I run the filegroup test initially the first run averages 20 seconds slower then the database sitting on the single file. However
on consective runs there is a significant improvement in performance with filegroups verses single files. I have
the indexes sitting on a separate filegroup and 2 additional filegroups to spread the tables across. Does any one know what would be causing
the performance to degrade on the initial run of the test. (The test by the way is a stored procedure that runs a select statement against each table).
Any Info Will help Thanks
Barb
Hi,
i'm writing a app in c# and have to store Trees in a Database.
I'm working with Datasets for the exchange between the DB and the App.
The trees have the same options like the windows folders. If u delete a node, all subnodes should be deleted too.
But something a Foreign Key from ParentID references (Id) with the delete-Rule on cascade seems not to be possible, because of multiple cascade Paths or cycles. Do i have to add some xtra constarins:
Not Possible:
create Table tree (
Id varchar Not null,
ParentId varchar Not null,
Constraint pk1 Primary Key (Id),
Constraint fk1 Foreign Key (ParentId) references tree(Id)
On Update Cascade
On delete CAscade
)
Do i have to write triggers, which delete The subnodes too and set the Update-/deleterulr on NO Action
Greetz
Does anyone know any good links for SQL tree structures and example queries and stuff... I cant really find anything part from the standard example of emplyee, boss, salary which explains how to create the tree table...(dun dis bit) I did notice a book but I live in a little village so cant go get it till wekend?
I'm desperate, reli need to work out how too do this.....
(Wow; surfing this site has really illuminated what a lowly hack-programmer I am to this field of SQL and relational processing :S )I am creating a temp table, doing an Bill of Material explosion for a single Order Line Item.(note: SQL Server 2000)I used blindman's "accumulator method (click here) (http://www.dbforums.com/showpost.php?p=6239109&postcount=6)" to generate the entire potential BOM tree.So; step 1 works wonderfully! :beer: Cheers blindman!Now; I want to remove unwanted nodes (and all their children) from the temp-table. I have a bunch of functions (0 to 1 for each node) that return a Yes or No. If "No", then flag-to-eliminate immediately that branch and don't revisit anything on it (and therein lies my problem). This will leave me with a temp table of only valid nodes.Since this is recursive, and since it will involve Dynamic SQL (the function name varies), all I can come up with is using a Cursor in a WHILE loop. Even at that; since a CURSOR is point-in-time (ie: values don't change once selected), I'll have to re-check the current temp table values (or create of 2nd temp table of only deleted nodes and repeat a SELECT with NOT EXISTS in it, hmmm).Since blindman's method generates a table ordered by level, the sequence of processing is pre-determined - unless I can re-order it into a more traditional hierarchy (1 entire branch at a time) and number the levels, in which case the cursor could just skip to the next branch of equal or higher level.Note: The thought does occur to me I could have an intermediary function (static name) that in turn does the Dynamic SQL. These functions contain the Business Logic that looks at a myriad of column values and relationships in the database and there's no one-size-fits-all decision tree so Dynamic SQL is necessary.The max cursor size will be maybe 300, and on average 100. Number of levels will normally be 3 or 4, but conceivably could be up to 10. Given the average 100 potential components/sub-assemblies, the final assembly will be about 30. As a periodic background process; it will do 3,000 Order Line Items a day, so I'm figuring 1 second response time per build is adequate (ie: the user's not waiting on it so it doesn't have to be blinding fast) - however why waste?Anyhow; I thought this might be a fun problem for some Data Structure genius who wants to give a lesson in Relational Programming.Thanks for looking.Here's what I have so far:CREATE PROCEDURE dbo.sp_ExplodeTest1 (@recID int = 1) AS/* tbTestH is a table containing an assembly hierarchy. Assemblies with no parent are Builds.Assemblies with no children are Components.It's columns: MyID int, ParentID int, (other descriptive columns)*/declare @t table (TempNodeID int identity(1,1),MyID int)-- Seed the tree with the Build's ID.insert into @t (MyID) values (@recID)/* This populates the temp table with the entire Assembly for the given build. It is Ordered By the level in the assembly. Number of assembly levels is infinite.For example: Level 1 = the Build. It comes firstLevel 2 = all parents are level 1. Is next (no particular seq)Level 3 = all parents are level 2. Is next (no particular seq)etc.*/while @@Rowcount > 0insert into @t (MyID)select tbTestH.MyID from tbTestHinner join @t rList on tbTestH.ParentID = rList.MyIDwhere not exists ( select *from @t CurrentList where CurrentList.MyID = tbTestH.MyID)-- now to display the results so far.select t.*, tbTestH.* from tbTestH inner join @t t on tbTestH.MyID = t.MyID order by t.TempNodeIDGO
View 8 Replies View RelatedWhat is included under SQL Data structures? Could anyone refer me to some good tutorials on that material?
View 2 Replies View RelatedI am using the wizard to create a new mining structure and getting the error at the time of selecting the datamining technique like either microsoft decision tree or microsoft timeseries etc.,
"Unable to retrieve a list of supported data mining algorithms. Make sure you are connected to the correct Analysis Services server instance and the Analysis Services server named localhost is running and configured properly. You can continue with a default list of data mining algorithms."
What is that means?
Thanks in advance,
hello, i am creating a user login system. When people register for the site I need a way for sql to check weather or not their is a duplicate username already in the databse. Currently, I am going through the whole member profile table searching for a duplicate name. Is there a better way to do this?
View 6 Replies View RelatedI want to make a, I want to make an exact replica of one database in sql server 2005 express edition so I have a database called dbTest and I want to create a database called dbTest2 that is the exact replica of dbTest.I am using sql server 2005 express edition.
View 3 Replies View RelatedIs there a way to copy/duplicate and rename a database on the same server? Ineed to make a copy for another application. I appreciate any tips or adviceyou can provide!TIA - Rob
View 3 Replies View Relatedhi,
I am using SQL Server 2000 and i wanted to know what is the best way to duplicate a database?
How do i remove duplicate records from a table with a single query without using cursors or anything like that.Sample :tempCol11221P.S The table has only one column
View 2 Replies View RelatedHi,
I am absolutely innocent as far as T-SQL is concerned. I need to detect all duplicates (key consists of 5 fields) in the table and delete the duplicates.
I tried different approaches like joins etc but nope.
Any help is appreciated
Thanks
ravi writes "plz tell me code for
1. i want to create a table with same structure and key value as an existing table.
2. How can i modify the primary key there i new table"
hi,
create TABLE #t ( id int,names varchar(50))
INSERT INTO #t VALUES(1,'master')
INSERT INTO #t VALUES(2,'tempdb')
INSERT INTO #t VALUES(2,'tempdb')
INSERT INTO #t VALUES(3,'model')
INSERT INTO #t VALUES(3,'model')
INSERT INTO #t VALUES(3,'model')
INSERT INTO #t VALUES(1,'master')
SELECT * FROM #t
Iam using sql server 2005. I want to delete the duplicate records from the table.Please specify a method which is very good at performance.
Thanks in Advance..........
Please Help me... How to create duplicate table dynamically(In Code). I have written query like this... select * into table2 from table1. But this creates only structure and data. I need to create along with constraints also(Primary key, default,index). Please help out me....
View 12 Replies View RelatedHi,Does any of u folks used any tools for database structures comparision??
I have used DBDIFF(from DKGAS.com) ChangeManager from Embarcadaro and SQL COMPARE from Redgate.
I did have have some problem in each of these tools AND am looking for some good tools which are economical also.
Thx in advance
Ravi
A point for general discussion maybe.
I've just read Jeff's query regarding passing an array to a stored procedure, and it seems to be a question that hits the message board quite frequently. There often seems to be the case where you have some block of data in one location, and want to process it somewhere else, and there just isn't an easy way to do it (or is there?). Passing a cursor is possible, but getting data back out of a cursor is painful.
The ways I have used in the past are:
Using a cursor
Using a global temporary table
Using a "permanent" table, but declared with a unique name (using a timestamp) and passing the tablename as a parameter.
Has anybody any suggestions for a future SQL release which we could suggest to Microsoft?
Suggestions from myself:
-Having a single TSQL command to copy a cursor back into a table, to make cursor parameters less cumbersome
-Being able to pass a pointer to a temporary table as an SP parameter
-Allowing variables to be declared with more complex structures (such as arrays, or classes)
Regards,
ChrisH
Hi, I have a table of folders that I would like to be able to displayin a depth first manner, similar to what you would see in WindowsExplorer. the table is defined similar toCREATE TABLE Folders (folderID int,parentFolderID int,name varchar(32)).... I've left some things out of the definition for the simplicity ofexample.My desire is to quickly generate a record set containing the all childfolders of a specific folder along with the how many levels deep eachfolder is, in a quick and memory efficient manner. I have done someresearch through this group and found a few examples similar to what Ihave hypothesized, however I was hoping for some feedback on what Ihave decided to use.Since this database table is already created and I cannot modify theexisting schema, it would not be very feasible to use a nested setsmodel (unfortunately -- or if anyone has a suggestion?).I plan on using a stored procedure that recusively calls itself.something similar to the following:/* This is assuming the table #TempFolderTable has* already been declared globally*/sp_FolderDisplayRecurive @folder int, @level int ASIF @@NESTLEVEL > 31 RETURN;DECLARE @childFolder INTDECLARE folders_cursor AS CURSOR LOCAL FORSELECT folderID FROM FoldersWHERE parentFolderID = @folderIDOPEN folders_cursorFETCH NEXT FROM folders_cursorINTO @childFolderwhile @@Fetch_status = 0BEGIN-- Insert current folder into-- folder table (under parent @folder)INSERT INTO #TempFolderTable(FolderID, DepthLevel, ParentFolderID) VALUES(@childFolder, @level, @folder)-- Iterate over all of its childrenEXEC sp_FolderDisplayRecursive@childFolder, @level + 1FETCH NEXT FROM folders_cursor INTO @childFolderEND...Will this incure a large overhead for a deep directory structure? Arethere any other problematic issues? Any suggestions and/or commentsare very welcome. Thanks!
View 3 Replies View Related