• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

SQL store procedure explain

I try to understand the below code to print out all the table.  However I have a few statement I am not quite understand can someone help.  What I don't understand it the MIN in the select statement and the 'AND' Clause (look like it doing some string comparison with '>'
      
        SET NOCOUNT ON
      DECLARE @TableName nvarchar(256)
      SET  @TableName = ''

      WHILE @TableName IS NOT NULL
      BEGIN
            SET @ColumnName = ''
            SET @TableName =
            (
                  SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                  FROM       INFORMATION_SCHEMA.TABLES
                  WHERE       TABLE_TYPE = 'BASE TABLE'
                          AND      QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            )
            Print @TableName
    end
0
tommym121
Asked:
tommym121
5 Solutions
 
gnoonCommented:
The sp returns a table name next to the specified table name ordered by name. If the specified table is the last one in list, returns NULL.

'>' compare two strings in binary data. Default collation of INFORMATION_SCHEMA.TABLES or its field is using for comparison logic (http://msdn.microsoft.com/en-us/library/ms189863.aspx).

For example, there are 5 tables in 'BASIC TABLE' type, and the specified table name is 'backupmediafamily'

backupfile
backupfilegroup
backupmediafamily
backupmediaset
backupset

The WHERE clause will look for table names that greater than 'backupmediafamily', which are {backupmediaset, backupset}.
MIN will pick up only one table name from {backupmediaset, backupset} determined by lowest VALUE in the sort sequence.

That's 'backupmediaset' is a table next to table 'backupmediafamily'. The sp returns 'backupmediaset'.

Hope you get it.
0
 
tommym121Author Commented:
gnoon

what is the MIN in select statement do?  Thanks
0
 
TempDBACommented:
Min is used to return the minimum value. The purpose of min is clear here as select statement can fetch more than one rows from the  INFORMATION_SCHEMA.TABLES, and set only takes a single value while assigning the value to a variable, the user is trying to fetch a single data. Though there are other ways of doing that, by selecting top 1 , but MIN funtion here is not used to get the actual theme what min does. The whole idea looks like getting a single table name with its schema.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
skaraiCommented:
First of all it'll be helpful to know against which database this code is running since results will vary depending on that. In addition this is an incomplete proc to begin with since @ColumnName variable is missing its definition. (You might as well comment it out since it's not doing anything to begin with.
Corrected looks like this - and if run against master returns the result below
SET NOCOUNT ON
      DECLARE @TableName nvarchar(255)
      --DECLARE @ColumnName nvarchar (255)
      SET  @TableName = ''

      WHILE @TableName IS NOT NULL
      BEGIN
            --SET @ColumnName = ''
            SET @TableName =
            (
                  SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                  FROM       INFORMATION_SCHEMA.TABLES
                  WHERE       TABLE_TYPE = 'BASE TABLE'
                  AND      QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            )
            PRINT @TableName
     END

-- Returns this result set from master DB
[dbo].[MSreplication_options]
[dbo].[spt_fallback_db]
[dbo].[spt_fallback_dev]
[dbo].[spt_fallback_usg]
[dbo].[spt_monitor]
[dbo].[spt_values]

Do not lose any sleep over this code :)
0
 
gnoonCommented:
MIN is aggregate function of SQL language in all database brands e.g. Microsoft SQL Server, Microsoft Access, Oracle, MySQL,  etc.

As TempDBA has explained, MIN is used to find the lowest value in data rows read from database. Googled "SQL MIN function" will give more info. There are many aggregate functions in SQL language; MIN, MAX, AVG, SUM, COUNT
0
 
santhimurthydCommented:
In the Store Procedure
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
 FROM  INFORMATION_SCHEMA.TABLES WHERE  TABLE_TYPE = 'BASE TABLE'

Return the list of table from the Information_schema which are all of type 'BASE TABLE' and then include the search operate 'like' to filter the table anme which are starts with @TableName

and that's equal to

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM  INFORMATION_SCHEMA.TABLES WHERE  TABLE_TYPE = 'BASE TABLE'
MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) like @TableName+'%'



0
 
tommym121Author Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now