Sql Server 2008: I Have 1000 Tables, I Need to Know Which Tables Have Data

SQL Server 2008: I have 1000 tables, I need to know which tables have data

A simpler syntax:

SELECT  
    [Name] = o.name,
    [RowCount]= SUM(p.row_count)
FROM SYS.DM_DB_PARTITION_STATS p
INNER JOIN SYS.TABLES o ON p.[object_ID] = o.[object_id]
WHERE index_id <= 1 -- Heap or clustered index only
GROUP BY o.name
ORDER BY 2 desc

View data of a table in sql server 2008

I'm assuming you are trying to view all the records in a table and there are more than 1000? In that case

SELECT * FROM <table name here>

or select the "select top 1000 rows" option and then just remove the "TOP 1000" from the query

SqlServer - Get all tables that has data ( are not empty )

Try this Script To get all tables with non empty records

    USE [Your database Name]
    Go
    SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
            [Tables].name AS [TableName]
            --SUM([Partitions].[rows]) AS [TotalRowCount]
    FROM sys.tables AS [Tables]
    JOIN sys.partitions AS [Partitions]
        ON [Tables].[object_id] = [Partitions].[object_id]
        AND [Partitions].index_id IN ( 0, 1 )
    -- WHERE [Tables].name = N'name of the table'
    GROUP BY SCHEMA_NAME(schema_id), [Tables].name
    HAVING SUM([Partitions].[rows]) >0

Get size of all tables in database

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC, t.Name

Find a string by searching all tables in SQL Server

If you are like me and have certain restrictions in a production environment, you may wish to use a table variable instead of temp table, and an ad-hoc query rather than a create procedure.

Of course depending on your sql server instance, it must support table variables.

I also added a USE statement to narrow the search scope

USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

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
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM @Results

SQL Server Management Studio - Finding all non empty tables

You could try using sysindexes and INFORMATION_SCHEMA.TABLES:)

SELECT 'Table Name'=convert(char(25),t.TABLE_NAME),
      'Total Record Count'=max(i.rows)
FROM sysindexes i, INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_NAME = object_name(i.id)
      and t.TABLE_TYPE = 'BASE TABLE'
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME
HAVING max(i.rows)>0
ORDER BY 'Total Record Count' DESC

Query to list number of records in each table in a database

If you're using SQL Server 2005 and up, you can also use this:

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    p.[Rows],
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY 
    object_name(i.object_id) 

In my opinion, it's easier to handle than the sp_msforeachtable output.

Records are missing in edit top 200 rows but can be viewed in select top 1000 rows SQL Server 2008 R2

Edit top 200 rows will do exactly that - so of course you are not going to see more rows than 200. Edit top 200 rows is not a good way to check if data exists in a table.

That said, you can increase the number of rows visible in the "Edit Top NNN" by doing the following:

In Sql Server Management Studio:

1. Open the "Tools" menu
2. Click "Options"
3. Expand "Sql Server Object Explorer"
4. Expand "Table and View options"
5. Change the value of "Value for Edit Top Rows Command" to the number of rows you want to appear when you edit.

If you are dealing with large tables, you are better off just doing the modifications with SQL rather than using the edit interface.

How to join two tables to select all data with and without the condition in MsSQL

You should use a FULL OUTER JOIN. Something like this...

SELECT *
FROM Table_Sale a
FULL OUTER JOIN Table_Return b ON a.S_Date = b.R_Date 
                              and a.S_Store = b.R_Store
                              and a.S_Item_ID = b.R_Item_ID

---

Related Topics