DB Details - Tables, Rows, Columns, Primary Keys

• Mohit Nayyar

  Ten Centuries

  Points: 1008

  More actions

  September 26, 2007 at 7:17 am

  #374820

  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

  More actions

  August 2, 2011 at 1:30 pm

  #1362556

  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