Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DB Details - Tables, Rows, Columns, Primary Keys Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2007 7:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:04 PM
Points: 148, Visits: 93
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"
Post #403005
Posted Tuesday, August 2, 2011 1:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 13, 2013 1:05 PM
Points: 1, Visits: 18
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
Post #1152970
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse