Solved

Getting data out of sql server efficiently

Posted on 2006-10-26
1
224 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 8

Accepted Solution

by:
redpipe earned 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.NET 2008 - SQL Timeout 9 51
defining NULL or 0 10 51
VS2013 publish C# application Created folder -- where is it??? 10 32
Convert Silverlight ERP To Angularjs,HTML5 3 61
Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

726 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