InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 15:55:57 GMT20http://www.sqlservercentral.com/Forums/Topic403005-613-1.aspxModified to add schema and to use sys.Tables [code="plain"]CREATE proc [dbo].[usp_DBDetailsRevised] AS SET NOCOUNT ONDECLARE @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 [/code]Tue, 02 Aug 2011 13:30:22 GMTpmpjr2k7http://www.sqlservercentral.com/Forums/Topic403005-613-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Miscellaneous/30773/">DB Details - Tables, Rows, Columns, Primary Keys</A>[/B]Wed, 26 Sep 2007 07:17:51 GMTMohit Nayyar