Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Getting data out of sql server efficiently

Posted on 2006-10-26
1
Medium Priority
?
230 Views
Last Modified: 2010-04-16
Hi Experts,

What I have to do is check about 100 tables in sql server to see if there have been any new records added after a specific date and time. Any changes detected must be reported to a front end GUI written in C#, the records will be displayed in a datagrid and later exported to XML.  Each table has a create date time column (Create_DTTM) So I need to loop through all the 100 table names and check if they have any records created after a certain date and time. If they do then select all of the record (select * from table) together with the column names and add it to some kind of "list", ideally inside sql server to aide performance rather than in the GUI application.  Once all of the tables have been checked display all the records in the GUI and then export to XML.  Any ideas on how to do this efficiently and dynamically using stored procedures or some other sql server method? Any suggestions on some kind of list and views on using a datagrid or some thing else. And any top tips on XML would be appreciated.

Regards,

Pete

0
Comment
Question by:Pete_Burke
1 Comment
 
LVL 8

Accepted Solution

by:
redpipe earned 2000 total points
ID: 17817484
I am a bit puzzled on how you plan to display records from the different tables in the GUI. I presume the tables have different look and feel (columnnames and types) and therefore giving you a hard time displaying records in the same grid (a GUI with about 100 grids would look a bit crowded :-)  But to your question:

There are at least 3 ways of getting information on available tables in your database:
1. SELECT * FROM INFORMATION_SCHEMA.TABLE WHERE table_type = 'BASE TABLE' AND table_name NOT LIKE 'sys%'
2. SELECT name FROM sysobjects WHERE type = ‘U’
3. EXEC SP_TABLES

The first is preferable since it promises compliance with ANSI standard. If MS internal/system tables changes the INFORMATION_SCHEMA views will change accordingly and your code will still work...

All available information about all columns:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Fully qualified name of all user tables:
SELECT table_catalog + '.' + table_schema + '.' + table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE' AND table_name NOT LIKE 'sys%'

A while ago I found a script similar to what you seek that was written by Erland Sommarskog, SQL Server MVP. It can still be found along with extensive explanations on the topic dynamic sql at http://www.sommarskog.se/dynamic_sql.html  For simpleness I also copied the script (see below). He uses by the way syscolumns access strategy. Obvious the INFORMATION_SCHEMA.COLUMNS can also be used. Choose yourself...
============ Start script ============

DECLARE @tbl    sysname,
        @sql    nvarchar(4000),
        @params nvarchar(4000),
        @count  int

DECLARE tblcur CURSOR STATIC LOCAL FOR
   SELECT object_name(id) FROM syscolumns WHERE name = 'LastUpdated'
   ORDER  BY 1
OPEN tblcur

WHILE 1 = 1
BEGIN
   FETCH tblcur INTO @tbl
   IF @@fetch_status <> 0
      BREAK

   SELECT @sql =
   N' SELECT @cnt = COUNT(*) FROM dbo.' + quotename(@tbl) +
   N' WHERE LastUpdated BETWEEN @fromdate AND ' +
   N'                           coalesce(@todate, ''99991231'')'
   SELECT @params = N'@fromdate datetime, ' +
                    N'@todate   datetime = NULL, ' +
                    N'@cnt      int      OUTPUT'
   EXEC sp_executesql @sql, @params, '20060101', @cnt = @count OUTPUT

   PRINT @tbl + ': ' + convert(varchar(10), @count) + ' modified rows.'
END

DEALLOCATE tblcur

============ End script ============
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question