September 26, 2007 at 7:17 am
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"
August 2, 2011 at 1:30 pm
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply