Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Oct 2003
    Posts
    47

    Unanswered: getting a table list

    Hello,


    If you need to have a liste of tables starting with 'WI', under Oracle for example, with a simple select of a system view (with a filter : table_name like 'WI%') you have the result.
    Under SQL Server, how to do this simply (sp_tables ?) ?

    Thanks in advance

  2. #2
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    use database_name
    go

    select * from sysobjects where xtype = 'u'
    order by name
    go

    Cheers

  3. #3
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Talking

    Sorry mate ---


    select * from sysobjects
    where xtype = 'u' and name like 'wi%'
    order by name
    go

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'WI%'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Brett,

    at what point does the INFORMATION_SCHEMA views get set up in a database

    Cheers

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's part of the installation...

    Can't you see them?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Can see them in the master database.

    I assume that you use these as templates and copy them accross to your user databases when you need them.

    Cheers

  8. #8
    Join Date
    Oct 2003
    Posts
    47
    Thanks all for the reply,

    Ok for using sysobjects :

    No, suppose that we would like to have the space used by tables ... Suppose that you need to list table having name startting with 'W' and using space > 100Mo ? and other information related to extents...

    (for these using Oracle, this information is find in 1 sys views, dba_segments).

  9. #9
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Hey dude,

    Not sure about a system table that will answer your question.

    Brett will know if there is.

    The way would do it would be to set up a stroed proc using sp_spacedused stored proc using string variables.

    declare @var1 as varchar (255)
    declare @strSQL as varchar (255)

    --set tablename
    set @var1 = 'yourTable'

    --set up dynamic SQL
    set @strSQL = 'sp_spaceused ' + @var1

    --excute the SQL
    exec (@strSQl)
    go


    you could set up a cursor to go through your sysobjects tables based on your criteria. All with name like 'w%' would be the select list for the cursor then populate another table with the results of the sp_spaceused.

    You can then query this table for your answer.
    Prop do it a faster way but I'm not sure and you could prop get all of this into one stroed proc.

    Cheers

  10. #10
    Join Date
    Oct 2003
    Posts
    47
    Thanks ... I agree with you (using of sp_usedspace ...).

    But it is not a simple way (sorry... I worked 3 years with Oracle and now I am discovering SQL Server ...).

  11. #11
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Wink

    give a couple a hours and see if anyone else comes back to you

    if not then I'll rustle up the stored proc

    might come in handy for me too !!!

  12. #12
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Which version of SQL are you using by the way ?

  13. #13
    Join Date
    Oct 2003
    Posts
    47
    Originally posted by aldo_2003
    Which version of SQL are you using by the way ?

    We are using SQL 2000

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by ENNAIFAR
    Thanks all for the reply,

    Ok for using sysobjects :

    No, suppose that we would like to have the space used by tables ... Suppose that you need to list table having name startting with 'W' and using space > 100Mo ? and other information related to extents...

    (for these using Oracle, this information is find in 1 sys views, dba_segments).
    Here's a base...you can modify the predicates in the cursor,
    or just apply them to the SELECT

    (Need conversion and stripping (I love to watch opposite gender stripping in action) the kb out of the data)

    The latter gives you all tables

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(
    	  [name] sysname
    	, [rows] int
    	, reserved varchar(50)
    	, data varchar(50)
    	, index_size varchar(50)
    	, unused varchar(50)
    )
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_sp_spaceused_all]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[isp_sp_spaceused_all]
    GO
    
    CREATE PROC isp_sp_spaceused_all
    AS
    BEGIN
    
    	SET NOCOUNT ON
    	DECLARE @SQL varchar(8000), @TABLE_NAME sysname
    	
    	DECLARE SpaceCSR CURSOR
    	FOR
    	SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables
    	 WHERE TABLE_TYPE = 'BASE TABLE'
    	OPEN SpaceCSR
    	
    	FETCH NEXT FROM SpaceCSR INTO @TABLE_NAME
    	
    	WHILE @@FETCH_STATUS = 0
    	
    	BEGIN
    	
    		SELECT @SQL = 'INSERT INTO myTable99([name],[rows],reserved,data,index_size,unused)' 
    		+' EXEC sp_Spaceused [' + @TABLE_NAME + ']'
    		EXEC(@SQL)
    		FETCH NEXT FROM SpaceCSR INTO @TABLE_NAME
    		END
    	
    	CLOSE SpaceCSR
    	DEALLOCATE SpaceCSR
    	
    	SELECT [name],[rows],reserved,data,index_size,unused
    	  FROM myTable99
    	
    	SET NOCOUNT OFF
    END
    GO
    
    EXEC isp_sp_spaceused_all
    GO
    
    --Clean up test environment
    
    DROP PROC  isp_sp_spaceused_all
    DROP TABLE myTable99
    GO
    Last edited by Brett Kaiser; 02-27-04 at 10:12.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Nice one Brett,

    Saved me from writing it .....

    You would think microsoft might include something like this in Sysobjects

    Oh well.....

    Cheers

Posting Permissions

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