How Do I Get A Printout Of All My Tables And Fields ?
Mar 15, 2001
Hi,
I have a database with approx 95 tables. I would like to get a printout of each table, the fields contained therein and the field type
(i.e the information displayed when rightclicking a table in Enterprise Manager and selecting Properties).
I have tried to use the diagram function but due to the large number of tables the diagram is very complex and would have to be printed out on a huge sheet of paper to make it legible. I have also looked at the SP_HELP stored procedure but can't seem to get the syntax correct to display properties on all tables (info on one table is no problem).
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu". can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
I have created a trigger in SQL Server 2000 that prints a message when someone tries to insert something in one of my tables.
it looks something like this:
create trigger trigger1 on table2 instead of insert as print 'Sorry - You cannot add anymore students!' go
my trigger works alright and I have tested it in SQL Server query analyser, and when I do an insert a message is shown instead of the insertion.
But the thing is that I have connected my database to a aspx-page that I have programmed in VB(Visual Studio). My question is, is there anyway that I can get the message that the trigger returns ('Sorry - You cannot add anymore students!' ) to be presented on my asp.net page in a label when someone tries to insert data in the table? Is it an exception ?
The SQL forums search keeps timing out, so I'm not able to check whether this has been asked previously. Is there a query/sproc available that will return Stored Procedure lines for a given database? I would like to make this available for printing via Access.
I'm not sure how easy of a task this is, or how to go about it, so I thought I'd ask here. I am doing a major overhaul to my site and am re-programming the backend which involves re-formatting my tables, table names etc.
What I want to do is set up a new table in the format I want for the new site, but still be able to have the live data that is being submitted to the site now, be entered into the new table so that I don't have to shut down the site for days to transfer and reformat data. Example
CURRENT SITE
Table 1 Field1: Name Field2: Address
NEW SITE
Table 1 Field1: Name Field2: myAddress Field3: City
I want to be able to somehow map the two together so that when data is entered into the Name field on the current site, that same name is also inserted into the new site table as well. Mapping like this:
(current site)Name -> (new site)Name (current site)Address -> (new site)myAddress
The city field would be left unaffected as that data would only be entered once the new site is up and running.
I know I could do this from a stored procedure on the exiting site or by re-writting all of my SQL to insert into both tables, but right now all of the SQL is hardcoded into the pages and I have A LOT of pages. Is there a way to map the tables together from within SQL Server?
I need to get the names of tables and fields (or atleast the fields) from a database. Is there a query I can use that will return what all the db's tables and those tables fields are?
I am working with the article that MAK wrote on SecurityLogs http://www.databasejournal.com/features/mssql/article.php/3515886
I have completed this, but I have made some changes to the database (for normalization to 3NF purposes). I now have problems with a query.
I am trying to "Insert a new record in a table if it does not already exist in the table". To try to clarify I perform the following query:
INSERT INTO Tmp_Event SELECT DISTINCT EventID, EventType, EventTypeName from Tmp
Which gives me the Tmp_Event table consisting of EventID's etc. (no duplicates). What I then want to do, is compare the 'Tmp_event' table and an already existing 'Event' table. These two tables are in fact identical. I would like to insert any records from 'Tmp_Event' into 'Event' if they do not already exist in 'Event'.
This query gives me all records that do not exist in 'Event'
SELECT EventID, EventType, EventTYpeName from Tmp_Event WHERE EventID NOT IN (SELECT EventID from Event)
How can I change this query into performing an INSERT INTO Event as well?
If possible, I would like to create a view using two different tables, but with the fields merged (ie. create one table from two tables). The table structures are pretty much the same. Can you do this from a singe SQL statement? Any ideas? My only alternative is creating a temp file that inserts records from both tables, but I want to avoid doing that.
There are more than hundred tables, so i wanna automate this. i am tryin to do this in a cursor: please guide!
declare @name VARCHAR (50) declare cur cursor fast_forward for select name from sysobjects where type='u' and status not like '-%' open cur WHILE (1=1) BEGIN FETCH NEXT FROM cur INTO @name IF @@fetch_status = 0 BEGIN ALTER TABLE @name ADD created_by [VARCHAR] (25) GO ALTER TABLE @name ADD created_by [VARCHAR] (25) GO ALTER TABLE @name ADD created_date [DATETIME] GO ALTER TABLE @name ADD modified_by [VARCHAR] (25) GO ALTER TABLE @name ADD modified_date [DATETIME] END ELSE BREAK END DEALLOCATE cur
I also want that if one column for a table exists; the other columns should be created rather than it quits.
For example, I have 2 tables that have very similar fields
Table 1 Table 2 ShipRpt ShipRpt invoice invoice total sold total sold
Is it possible to compare each of these fields and return any record that has non matching data? I would join the tables on the shipping report or invoice and only compare the total sold field. If the invoices match and the total sold is different in each table, can I just return the 1 record?
I am using a hosted MSSQL 2000 database that powers the backend of mywebsite. Website visitors interact with it via ASP pages I havedeveloped. I also have an internal FileMaker 7 database thatperiodically synchs with it via Filemaker's ODBC functionality.Several times now, I have come in one day to discover that my ASP pagesdon't work. When I look into it, one of my MSSQL tables will be missinga few fields. There is nothing in any of my code that sends an ALTERTABLE command or any other command that affects table structure. Thesedeletions of fields is totally random.The most recent example was with a table called FreeTeacherSubs. I justdiscovered today that three fields went missing:HowDidYouHearAboutYES TEXTFollowUpCallOrEmail TEXTHowMaterialsFit TEXTSuffice to say I am baffled! Has anyone ever experienced fieldsdisappearing from their tables?Kevin
I feel sure that this is a very naive question, but it is freeky me out that I can solve it. With SQL server what is the easiest way to make a field other than the Primary key unique?Would appreciate help with this. ThanksPaul
Hi there, I have a fairly generic question regarding database design. I am working on my first database using SQL Express. I am testing the database by inserting records into the pre defined tables. Sometimes I forget to input text into a non null field. When this happens it displays an error and then skips a few rows which makes my row numbering messy. For example say I have Row 1, Row 2 Row 3 all nicely formatted but on Row 4 I make a mistake this row then disapears and their is gap between 3 and 5 where 4 was. Although I am just testing, I was wondering what happens in a real scenario if an inexperienced data input person makes a few mistakes it makes the database numbering systems look bad. In this case is it common to make most fields NULL to allow for admin errors??
Im writing a program in ASP language which it is using SQL Server for database.
I want to know how to create a link between fields of two tables. For example: I have a table named: Projects containing: p_name status date member1 member2
and another table named results containing: p_name s_status status1 status2
I want to create a link between field s_status of STATUS table and field status of table Projects
Users are sending data to table projects, when a user send some data to status of projects, I want this link to send data from status in project to s_status of results.
Question.I have a new table that I am adding to a script that I wrote. Thistable has 3 fields, the first 2 fields are used in the on statement asbeing = other fields in the script.The first field always has data in it, but the 2nd field is sometimesnull.So my problem is if both fields have data in them and they both matchto the data in the fields that I am linking them to, then it returnsthe 3rd field without a problem. However if the 2nd field is null thenit is returning a null for the 3rd field. I have checked and the fieldthat I am linking to is null also.So if I haveselect t1.field1, t1.field2, t2.field1, t2.field2, t2.field3from table1 t1join table2 t2on t1.field1=t2.field1 and t1.field2=t2.field2with 2 records in each tabletable1: record1: data, datarecord2: data, nulltable2: record1: data,data,datarecord2: data,null,datawhat I get from the script isrecord1: data, data,data,data,datarecord2: data,null,data,null,nullI would expectrecord2: data,null,data,null,dataI hope this makes sense, I didn't want to post the entire actual scriptas it is about 150 lines long.Thanks in advance.
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.
I have the following cursor that I am comparing 2 tables, theproduction table and a copy of the production table, I want results ofall address's that are like the address1 field...the problem is...myresults are giving me every field and if there is more than one, it isputting it in a grid....I only want to see results if they are 1 for the same address fieldthis is what I have so far....declare @address1 char(61),@city char(61)declare address_cursor CURSOR FORSELECT address1,city FROM test.dbo.testaddOPEN address_cursorfetch next from address_cursor into @address1,@citywhile @@fetch_status = 0BEGINselect * from testadd where @address1 like '%' + address1 + '%' and@city = cityFetch next from address_cursor into @address1,@cityPrintENDCLOSE address_cursorDEallocate address_cursor
Just when I though I knew what I was doing. I ran into a roadblock.I have two tables, organizations & usersI am building a form online for orgs to input their own information.How do I insert into both tables when the org table needs to know what theuser_id is, but the user_id hasn't been created until the form is submitted?Any help or request for additional info would be more than great.Thanks!
Can someone show me what I need to do to have a result set only displaying records from the specified date range. All the tables can relate on Date, shift, and ProductType (Besides UnprocessedProductTracking which can only relate on Date and Shift) I have a lot of trouble with joins...well I'm just not to experienced. Help!
I want to log errors to a table. If the error is with a URL, I want to store the URL. These URLs can be very large, hundreds of characters, but I only need to store it if it causes the error, which should be very infrequent. Which is the better design:
Create a large varchar field in the log table to hold the URL, or null if the error wasn't with the URL. Create a foreign key field in the log table to a second URL table, which has a unique ID and a large varchar, and only create a record in this table if the error is with the URL.
One concern I have with design 2 is that there could be many other fields that are infrequent. Do I create a separate table for every one?
i have a database with 300 tables. All the data types of the fields of my tables are custom. Ex. IFGint:int and stuff like that.
I want to know how can i through a stored procedure, change in batch mode all the fields of my tables. I don't want to modify by hand everytable. It's a lot of work and i think that maybe there might be a way for this programatically.
I have develop big Database System in Microsoft SQL Server 2000 and my DB System content more then 30 Table and also more then 150 Stored procedure and from that stored procedures I have call my the tables
But the problem when I change the name of any table, SQL Server don’t prompt my about the stored procedure that use that table .. so I cant remember witch Stored procedure use that table
By example if I am in programming language like C# or VB.NET when I try to change any name of any class the compiles catch all the error that coming from changing the name of the class and simply I will fix the problem because the compiler help me and list all the object content the error
But in SQL Server how can I search for stored procedure that use un existing table like compiler in any programming language ??
Or is there any why to solve my problem ??
And the above problem also there when I change the filed name
I have Database Library, which has a lot of tables and we need 3 tables for quary:
Table Librarians: field ID, field Surname; Table StudentCard: field ID, foreign key on table Librarians and other fields, which we don't use Table TeacherCard: field ID, foreign key on table Librarians and other fields, which we don't use
Query: Select the librarian's surname, which gave the most count of books.
I know, how to resolve, when i took datas only from one table, e. g. TeacherCard
SELECT TOP 1 WITH TIES Librarians.LastName, MAX(Librarians.CountOfBooks) AS Books FROM (SELECT L.LastName, COUNT(*) AS CountOfBooks FROM Libs L, T_Cards T WHERE T.Id_Lib IN (SELECT L.Id) GROUP BY L.LastName) AS Librarians GROUP BY Librarians.LastName ORDER BY MAX(Librarians.CountOfBooks) DESC GO
I dont know, how to use datas from TeacherCard and from StudetnCard at the same time.
I would like to create the trigger on tblCaseBillingDetails so that when the data in the Defendant fields are updated, the trigger fires and updates the Defendant count DefCount.
Consider a situation. There is a table of submitted 'documents'. They have some attributes. There are assignments to process the things, which have a date they were created. Finally there is a price list which specifies the price according to document features and date, so that the assignment to process a document created at different time will have a different cost. In other words, there is a relation (assignment->document.attribute(s) + assignment.date) -> pricelist.price
Creating relations has the integrity advantages: it is not possible to create an assignment, which price is not defined in the pricelist; precludes the pricelist entry removal if it is referred by any assignments.
Should a view, which combines all the foreign fields into one virtual table, be created to make establishing the reference possible?
I want to compare two tables and log the difference in new table with the fields as (old value,new value, column name). The column name should be the changes value column.
