Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2011
    Posts
    29

    Question Unanswered: Loop through Different Objects in a Database ?

    Hi Everyone,

    I have an ActiveX script which I need to convert to a T-SQL Stored Procedure. As a part of it, I need to loop through all the tables in a Database and check whether the particular Table/View exists? If doesn't exist, I need to create one with Primary Key, Foreign Keys, Indexes (Clustered & Non-Clustered), and Check Constraints. Can somebody help me out with that?

    Here is the ActiveX Script I have:

    Code:
    Dim sNextMonthTable 
    Dim oServerName
    Dim oTables
    Dim CheckDate
    
        sNextMonthTable = "Jan_2012"
        Set oServerName = CreateObject("SQLDMO.SQLServer")
        Set oDB = CreateObject("SQLDMO.Database")
        Set oTable = CreateObject("SQLDMO.Table")
        oServer.LoginSecure = True
        oServerName.Connect sServer
        Set oDB = oServerName.Databases(sDatabase)
        CheckDate = True
        For Each oTable In oDB.Tables
            If oTables.Name = sNextMonthTable Then 
               CheckDate = False
            End If
    	Next

    Thanks in Advance
    Mr.Bean

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    With this script you can get all tables from a database:
    Code:
    select * from sys.tables
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Dec 2011
    Posts
    29

    Unhappy

    Quote Originally Posted by Wim View Post
    With this script you can get all tables from a database:
    Code:
    select * from sys.tables
    Sorry to say that but it throws me an error:

    Code:
    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'sys.tables'.
    Also, its not just list the tables I need to do, I need to write a Proc so that every month it loops through the database and finds out whether the table for that particular month exists?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Since you are using SQL-DMO instead of ADO, I'll assume that your SQL Server is running SQL Server 2000 or earlier. The SQL-DMO code that you posted will return TRUE if a table exists with the name stored in the sNextMonthTable variable.

    This is a bad coding practice. Since you need to re-write this code anyway, I would fix the underlying design problems now. The only reason that I'd continue to use this design would be if you had to keep some of the existing VBA code running for political or practical reasons (I can't imagine any valid technical reasons).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2011
    Posts
    29

    Cool

    Quote Originally Posted by Pat Phelan View Post
    Since you are using SQL-DMO instead of ADO, I'll assume that your SQL Server is running SQL Server 2000 or earlier. The SQL-DMO code that you posted will return TRUE if a table exists with the name stored in the sNextMonthTable variable.

    This is a bad coding practice. Since you need to re-write this code anyway, I would fix the underlying design problems now. The only reason that I'd continue to use this design would be if you had to keep some of the existing VBA code running for political or practical reasons (I can't imagine any valid technical reasons).

    -PatP
    Hi Pat,
    Yes you are right. I am using SQL Server 2000. And I need to rewrite the query into a T-SQL Stored Procedure. So, the best practice with SQL Server 2000 T-SQL should be fine. Please help me out with that.


    Thanks
    Mr.Bean

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Sorry to say that but it throws me an error:
    Try
    Code:
    select * from sysobjects where xtype=u
    Also, its not just list the tables I need to do, I need to write a Proc so that every month it loops through the database and finds out whether the table for that particular month exists?
    I thought my previous code would have got you started.
    Code:
    DECLARE @sNextMonthTable VARCHAR(100)
    SET @sNextMonthTable = 'Jan_2012'
    
    if EXISTS (SELECT 1 from sysobjects where xtype=u AND name = @sNextMonthTable)
    	PRINT 'Found'
    ELSE
    	PRINT 'Not Found'
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You'll need to understand the purpose of this test in order to find the "best practice". The most obvious purpose would be for the table to act as a "sentinal" to tell code that some process either was running or that it had been run. If that is the case, you want to form something analagous to a "run information" table that contains information about when/whether code has run, its status (success, failure, other), maybe who ran it, etc.

    Without knowing exactly what the existance of this table signifiied to your VBA code, I don't know what the Transact-SQL replacement ought to do. Coach me a bit on what your code needs to know and what that table's existance tells your code and then I can help you craft a better Transact-SQL replacement for your VBA/SQL-DMO code.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Dec 2011
    Posts
    29
    Quote Originally Posted by Wim View Post
    Try
    Code:
    select * from sysobjects where xtype=u
    I thought my previous code would have got you started.
    Code:
    DECLARE @sNextMonthTable VARCHAR(100)
    SET @sNextMonthTable = 'Jan_2012'
    
    if EXISTS (SELECT 1 from sysobjects where xtype=u AND name = @sNextMonthTable)
    	PRINT 'Found'
    ELSE
    	PRINT 'Not Found'
    Hey Wim,

    So, instead of looping through a database, it checks the existence of particular table in one shot right?

    If it is so, thats Fantastic. Seems to be a very simple query. Thanks a lot for this one. However, I could see only the tables in the Database and couldn't see the Views when I use this query. Can you also help me out how to retrieve the Views.

    Thanks
    Mr.Bean

  9. #9
    Join Date
    Dec 2011
    Posts
    29
    Quote Originally Posted by Pat Phelan View Post
    You'll need to understand the purpose of this test in order to find the "best practice". The most obvious purpose would be for the table to act as a "sentinal" to tell code that some process either was running or that it had been run. If that is the case, you want to form something analagous to a "run information" table that contains information about when/whether code has run, its status (success, failure, other), maybe who ran it, etc.

    Without knowing exactly what the existance of this table signifiied to your VBA code, I don't know what the Transact-SQL replacement ought to do. Coach me a bit on what your code needs to know and what that table's existance tells your code and then I can help you craft a better Transact-SQL replacement for your VBA/SQL-DMO code.

    -PatP
    Well actually I have to convert an Active X script in a DTS Package to a Stored Proc. As a part of it, in one package the ActiveX script loops through the objects in a database and checks the existence of the current month table. If the table exists, the package execution fails. If it doesn't exist then the table for that particular month should be created with the constraints, keys and indexes.

    Hope you understood the scenario.

    Thanks
    Mr.Bean

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Mr.Bean
    I could see only the tables in the Database and couldn't see the Views when I use this query. Can you also help me out how to retrieve the Views.
    Code:
    ...
    if EXISTS (SELECT 1 from sysobjects where xtype IN ('U', 'V') AND name = @sNextMonthTable)
    ...
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Why are there separate tables for each month? This is a fundamentally bad practice because whatever gets stored in these tables (clients, products, folders, whatever) can probably be handled better by creating them in a single table with an "as of" date or an "effective month" column.

    Creating the table and failing if it has already been created is simple. The problem that I have is with the conecept itself... While it might have made sense at some point in time because it was easy to do, I will nearly guarantee that it is a problem waiting to happen!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Dec 2011
    Posts
    29
    Quote Originally Posted by Wim View Post
    Code:
    ...
    if EXISTS (SELECT 1 from sysobjects where xtype IN ('U', 'V') AND name = @sNextMonthTable)
    ...
    Once again Thanks a lot Wim

  13. #13
    Join Date
    Dec 2011
    Posts
    29
    Quote Originally Posted by Pat Phelan View Post
    Why are there separate tables for each month? This is a fundamentally bad practice because whatever gets stored in these tables (clients, products, folders, whatever) can probably be handled better by creating them in a single table with an "as of" date or an "effective month" column.

    Creating the table and failing if it has already been created is simple. The problem that I have is with the conecept itself... While it might have made sense at some point in time because it was easy to do, I will nearly guarantee that it is a problem waiting to happen!

    -PatP
    Well each month table has the purchases made by customers in the respective months. A track of record is maintained for every month and thats the reason a new table is created every month. The data is pulled from the server after every month and the data is stored for future purpose.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •