Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Getting data out of sql server efficiently

Posted on 2006-10-26
1
Medium Priority
?
228 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 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…
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!
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

609 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