Wednesday, 7 September 2011

SQL - How do I determine if a table exists in a SQL Server database?


For normal tables, you can use this query (SQL Server 2000):
 
IF EXISTS (SELECT 1
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE='BASE TABLE'
    AND TABLE_NAME='tablename')
        SELECT 'tablename exists.'
ELSE
        SELECT 'tablename does not exist.'

-- or

DECLARE @isObject TINYINT, @isTable TINYINT, @objID INT
SET @objID = OBJECT_ID('tablename')
SELECT @isObject = COALESCE(@objID, 0),
    @isTable = OBJECTPROPERTY(@objID, 'IsUserTable')
IF @isObject + @isTable = 2
    SELECT 'tablename exists.'
ELSE
    SELECT 'tablename does not exist.' 

In SQL Server 7.0, you can query sysobjects:
 
IF EXISTS (SELECT 1
    FROM sysobjects
    WHERE xtype='u' AND name='tablename')
        SELECT 'tablename exists.'
ELSE
        SELECT 'tablename does not exist.' 

Now you may have been stuck for #temp tables. Many people have tried this:
 
SELECT * FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME='#some_temp_name'

-- or

SELECT * FROM sysobjects WHERE name='#tmp'

These don't work for two reasons. One is that #temp tables don't live in the current database, but rather in TempDB. So, add the TempDB prefix, or USE TEMPDB statement, right? No, there is still another reason this won't work. Much like session variables in ASP, TempDB needs some way to track #temp tables in the event that several people create #some_temp_name at the same time. So, an identifier is appended to the table name, something like:
 
#some_temp_name__...many_underscores_removed...__000000000016

You can see this name simply by running the following:
 
USE PUBS
GO

CREATE TABLE #some_temp_name(id INT)
GO

USE TEMPDB
GO

SELECT OBJECT_NAME(OBJECT_ID('#some_temp_name'))

USE PUBS
GO

Okay, with all that out of the way, here is how you can use the OBJECT_ID function to determine if a #temp table exists:
 
IF OBJECT_ID('tempdb..#some_temp_name') IS NOT NULL
    PRINT '#some_temp_name exists.'
ELSE
    PRINT '#some_temp_name does not exist.'

You can use this methodology for normal tables as well, but keep in mind that, just because a name returns a valid OBJECT_ID, doesn't mean that it is a table (unless you have a very adherent development staff, and/or can otherwise guarantee that by naming scheme alone).

Note that you can't use OBJECT_NAME(OBJECT_ID('tempdb..#tempname')) in the context of the current database. The OBJECT_ID will return correctly, since it is handed an object that is clearly in tempdb. However, OBJECT_NAME uses the current context, and is only handed an integer. So you will either end up with NULL, or the name of some object in the current database that corresponds with the id returned from the inner function call.

For information on checking for a table's existence in Microsoft Access, see
Article #2350.





Can I start IDENTITY values at a new seed?

Increase Identity value manually
This can be useful if you want to create an artificial gap between your current seed and your desired next identity value.
 
SET IDENTITY_INSERT [myTable] OFF
INSERT INTO [myTable] (id,other)
    VALUES(@NewSeed,'something')
SET IDENTITY_INSERT ON [myTable] ON 

In this case, the id column will continue incrementing from the vaue of @NewSeed.

If you want to remove all entries in a table and start over at 1 (or a non-default seed), you can do one of two things:
 
DROP TABLE [myTable]
CREATE TABLE [myTable]
(
    -- Column definition
)

-- or

TRUNCATE TABLE [myTable]

Note that TRUNCATE TABLE will not work in particular scenarios, e.g. if another table has a foreign key constraint pointing at the table you're trying to reset (even if there is no data representing that relationship). What you can do instead is:
 
DELETE [myTable] -- assuming you want to clear the data
DBCC CHECKIDENT('myTable', RESEED, 0)

The final parameter is a little awkward. If you want to make sure that the next IDENTITY value that gets generated is 1, you set this value to 0 (this is the typical case). If you want to inject a gap into the IDENTITY sequence, let's say you want the next IDENTITY to be 25000, you would say:
 
DBCC CHECKIDENT('myTable', RESEED, 24999)

Of course, that all assumes that your increment value is 1. If your increment value is 50, you would use 24950 instead of 24999.

It's not a great idea to reseed an IDENTITY sequence *lower* than any values that currently exist in the table. You might get a little surprise when your counter gets back up that high and hits an existing value... 




How do I get a list of SQL Server tables and their row counts?
Obvious methods

The most straightforward method for determining the number of rows in a table, is to use the following:
 
SELECT COUNT(*) FROM tablename

You can also use the system stored procedure, sp_spaceused, to determine other information, such as data and index size:
 
EXEC sp_spaceused 'tablename'

To get an *approximate* count for all tables, you can use the following:
 
SELECT
    [TableName] = so.name,
    [RowCount] = MAX(si.rows)
FROM
    sysobjects so,
    sysindexes si
WHERE
    so.xtype = 'U'
    AND
    si.id = OBJECT_ID(so.name)
GROUP BY
    so.name
ORDER BY
    2 DESC

The sysindexes table is usually a little bit inaccurate, because it is not updated constantly. It will also include the 'dtproperties' table, which is one of those hybrid tables that falls neither under the 'system' nor 'user' category. It does not appear in Enterprise Manager's "Tables" view if you choose to hide system objects, but it shows up above.

In any case, it is generally not recommended to query against the system objects directly, so please only use the above for rough, ad-hoc guesstimates.

Undocumented methods

Please don't rely on these methods, or use them in production code. Undocumented stored procedures may change or be disabled in a future release, or even a service pack / hotfix; or, they could disappear altogether.

The following creates your own diagnostic page to give you a quick overview of how many rows are in each table in a specific database. It uses my favorite of the undocumented, do-not-use-in-production system stored procedures, sp_MSForEachTable:
 
CREATE PROCEDURE dbo.listTableRowCounts
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @SQL VARCHAR(255)
    SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
    EXEC(@SQL)

    CREATE TABLE #foo
    (
        tablename VARCHAR(255),
        rc INT
    )
   
    INSERT #foo
        EXEC sp_msForEachTable
            'SELECT PARSENAME(''?'', 1),
            COUNT(*) FROM ?'

    SELECT tablename, rc
        FROM #foo
        ORDER BY rc DESC

    DROP TABLE #foo
END

(The only reason a #temp table is used here is because we want the results ordered by largest row counts first. If the order can be arbitrary, you can just run the EXEC by itself.)

If you want to run it from ASP, you can call it as follows:
 
<%
    set conn = CreateObject("ADODB.Connection")
    conn.open "<connection_string>"
    set rs = conn.execute("EXEC dbo.listTableRowCounts")
    if not rs.eof then

        response.write "<table><tr>" & _
            "<th>Table name</th>" & _
            "<th>Rows</th></tr>"

        do while not rs.eof
            response.write "<tr>" & _
                " <td>" & rs(0) & "</td>" & _
                " <td>" & rs(1) & "</td>" & _
                "</tr>"
            rs.movenext
        loop

        response.write "</table>"
    end if
    rs.close: set rs = nothing
    conn.close: set conn = nothing
%>

Note that this will only count USER tables, not system tables. You could consider creating this procedure in the master database and marking it as a system object; this way, you could execute it within the context of any database, instead of having to create a copy of the proc for each database.

Replicating 'Taskpad / Table Info' view

Several people have asked how to mimic what taskpad view in Enterprise Manager does for Table Info, without having to scroll or search to find tables, and without listing all of the (largely superfluous and mostly built-in) index names. This view shows all the tables, rowcounts, reserved size and index size. Here is a stored procedure that does it one better... it essentially fires an sp_spaceused (which includes data and free space, in addition to reserved and index size). Now, before you use it, please exercise caution. This relies on system tables, and the undocumented sp_msForEachTable. Its behavior may change between versions and service packs, so don't rely on it for production code.
 
CREATE PROCEDURE dbo.allTables_SpaceUsed
AS
BEGIN
    SET NOCOUNT ON    

    DBCC UPDATEUSAGE(0)

    CREATE TABLE #t
    (
        id INT,
        TableName VARCHAR(32),
        NRows INT,
        Reserved FLOAT,
        TableSize FLOAT,
        IndexSize FLOAT,
        FreeSpace FLOAT
    )

    INSERT #t EXEC sp_msForEachTable 'SELECT
        OBJECT_ID(PARSENAME(''?'',1)),
        PARSENAME(''?'',1),
        COUNT(*),0,0,0,0 FROM ?'

    DECLARE @low INT

    SELECT @low = [low] FROM master.dbo.spt_values
        WHERE number = 1
        AND type = 'E'

    UPDATE #t SET Reserved = x.r, IndexSize = x.i FROM
        (SELECT id, r = SUM(si.reserved), i = SUM(si.used)
        FROM sysindexes si
        WHERE si.indid IN (0, 1, 255)
        GROUP BY id) x
        WHERE x.id = #t.id

    UPDATE #t SET TableSize = (SELECT SUM(si.dpages)
        FROM sysindexes si
        WHERE si.indid < 2
        AND si.id = #t.id)

    UPDATE #t SET TableSize = TableSize +
        (SELECT COALESCE(SUM(used), 0)
        FROM sysindexes si
        WHERE si.indid = 255
        AND si.id = #t.id)

    UPDATE #t SET FreeSpace = Reserved - IndexSize

    UPDATE #t SET IndexSize = IndexSize - TableSize

    SELECT
        tablename,
        nrows,
        Reserved = LTRIM(STR(
            reserved * @low / 1024.,15,0) +
            ' ' + 'KB'),
        DataSize = LTRIM(STR(
            tablesize * @low / 1024.,15,0) +
            ' ' + 'KB'),
        IndexSize = LTRIM(STR(
            indexSize * @low / 1024.,15,0) +
            ' ' + 'KB'),
        FreeSpace = LTRIM(STR(
            freeSpace * @low / 1024.,15,0) +
            ' ' + 'KB')
        FROM #t
        ORDER BY 1

    DROP TABLE #t
END

No comments:

Post a Comment