DECLARE
@TableName varchar(1000)
SET @TableName = ''
DECLARE TableCursor CURSOR FOR
SELECT [Name] AS [Table Name]
FROM sysObjects
WHERE xType = 'U'
ORDER BY [Name]
OPEN TableCursor
FETCH NEXT FROM TableCursor
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT DISTINCT
lower(Obj.Name) AS TableName
,Col.Name AS ColumnName
,St.Name AS DataType
,Col.Length AS FieldSize
,IsPk =
CASE
WHEN Left(ISC.constraint_name,2) ='PK'
THEN 'Y'
ELSE '-'
END
,IsAutoNum =
CASE
WHEN Col.Autoval Is Not Null
THEN 'Y'
ELSE '-'
END
FROM sysObjects Obj
INNER JOIN sysColumns Col
ON Obj.ID = Col.ID
INNER JOIN systypes ST
ON ST.Xtype = Col.Xtype
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ISC
ON TABLE_NAME = @TableName AND Col.[Name] = ISC.COLUMN_NAME
WHERE obj.xtype = 'U' AND obj.[Name] = @TableName
DECLARE @SQL varchar(200)
SET @SQL = 'SELECT ''' + @TableName +' Count = '' + cast(Count(*) as varchar) AS '''+@TableName+' COUNT'' FROM ' + @TableName
EXEC(@SQL)
FETCH NEXT FROM TableCursor
INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Label List
- Architecture
- Art of SQL Server
- asynchronous mirroring
- csv
- dmv
- find outdated Statistics
- Free SQL Server tools
- index fragmentation
- Installation
- Katmai
- Learn SQL Server 2005
- Maintenance Plan
- outdated Statistics
- Reset Identity column
- Scripts
- SPID
- sql server 2008
- sys.dm_db_index_physical_stats
- sys.dm_exec_connections
- tsql
- update statistics