Hi,
I would like to execute the below statment using python on sql server. The statment is working on sql sqerver. But when i tried to execute as sql from python it fails. Any idea how to run in directly?!
I would like to execute the below statment using python on sql server. The statment is working on sql sqerver. But when i tried to execute as sql from python it fails. Any idea how to run in directly?!
Output:#sqlserver #storedprocedure
--List the DBName, SchemaName, table Name, ROWCOUNT, DATA SIZE
--for ALL the database in specific server
declare @SprocsTableCount as Table (
dbname varchar(128),
SchemaName varchar (128),
ObjectName VarChar(128),
rowscount int,
DataSize int,
createddate datetime,
modifydate datetime
)
insert into @SprocsTableCount
execute sp_MSforeachdb N'use [?]; select ''?''
,SchemaName=s.name
,TableName=t.name
,p.rows
,DataInKB=sum(a.used_pages)*8
,CreateDate=t.create_date
,ModifyDate=t.modify_date
FROM sys.schemas s
JOIN sys.tables t on s.schema_id=t.schema_id
JOIN sys.partitions p on t.object_id=p.object_id
JOIN sys.allocation_units a on a.container_id=p.partition_id
GROUP BY s.name, t.name, t.create_date, t.modify_date, p.rows
ORDER BY SchemaName, TableName'
select dbname as DbName,SchemaName as SchemaName,ObjectName,rowscount as Rows,DataSize as DataInKB
,createddate as CreateDate,modifydate as ModifyDate from @SprocsTableCount
order by dbname