DB Details - Tables, Rows, Columns, Primary Keys

  • Mohit Nayyar

    Ten Centuries

    Points: 1008

    Comments posted to this topic are about the item DB Details - Tables, Rows, Columns, Primary Keys

    Thanks
    Mohit Nayyar
    http://mohitnayyar.blogspot.com/
    "If I am destined to fail, then I do have a purpose in my life, To fail my destiny"

  • pmpjr2k7

    Grasshopper

    Points: 24

    Modified to add schema and to use sys.Tables

    CREATE proc [dbo].[usp_DBDetailsRevised]

    AS

    SET NOCOUNT ON

    DECLARE @id INT, @cnt INT

    DECLARE @FullName VARCHAR(255), @SchemaName VARCHAR(255), @TableName VARCHAR(255), @sql NVARCHAR(4000), @temp VARCHAR(900), @pcol VARCHAR(255)

    CREATE TABLE #temptable (

    TableName VARCHAR(255)

    , TotalColumns INT

    , TotalRows INT

    , PrimaryKeyCols VARCHAR(900)

    )

    DECLARE tempCursor CURSOR FOR

    SELECT schema_name(schema_id) + '.' + [name] AS [FullName]

    , schema_name(schema_id) AS [SchemaName]

    , [name] AS [TableName]

    , object_id AS id

    FROM sys.Tables

    WHERE [name] NOT LIKE 'sys%'

    AND [name] NOT LIKE 'SSIS%'

    OPEN tempCursor

    FETCH NEXT FROM tempCursor INTO @FullName, @SchemaName, @TableName, @id

    WHILE(@@fetch_status=0)

    BEGIN

    SET @cnt=0

    SET @sql='select @cnt = count(*) from ' + @FullName

    EXEC sp_executesql @sql, N'@cnt int out', @cnt OUT

    SET @temp=''

    DECLARE intemp CURSOR FOR

    SELECT a.Column_Name

    FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a, INFORMATION_SCHEMA.TABLE_CONSTRAINTS b

    WHERE b.constraint_type='PRIMARY KEY'

    AND a.constraint_name = b.constraint_name

    AND a.table_schema = LTRIM(RTRIM(@SchemaName))

    AND a.table_name=LTRIM(RTRIM(@TableName))

    OPEN intemp

    FETCH NEXT FROM intemp INTO @pcol

    WHILE(@@FETCH_STATUS=0)

    BEGIN

    SET @temp = @temp + ',' + @pcol

    FETCH NEXT FROM intemp INTO @pcol

    END

    CLOSE intemp

    DEALLOCATE intemp

    SET @temp='''' + SUBSTRING(@temp,2,900) + ''''

    SET @sql='insert into #temptable (TableName, PrimaryKeyCols, TotalRows, TotalColumns) values (''' + @FullName + ''',' + @temp + ',' + cast(@cnt as varchar) + ','

    SELECT @cnt=count(*)

    FROM sysColumns

    WHERE id=@id

    SET @sql=@sql + cast(@cnt AS VARCHAR) + ')'

    EXEC sp_executesql @sql

    FETCH NEXT FROM tempCursor INTO @FullName, @SchemaName, @TableName, @id

    END

    CLOSE tempCursor

    DEALLOCATE tempCursor

    SELECT *

    FROM #tempTable

    ORDER BY tablename

    DROP TABLE #temptable

    SET NOCOUNT OFF

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply