-- =====================================================
-- Script for:
-- table size information query.
-- single database, can Ignore schema name
-- =====================================================
SET NOCOUNT ON;
DECLARE
@size__unit decimal(24, 20),
@flag_check_server bit,
@request_tables nvarchar(max)
;
SELECT
@size__unit = 8, -- KB
@size__unit = @size__unit / 1024, -- MB
@size__unit = @size__unit / 1024, -- GB
@flag_check_server = 0,
@request_tables = N'
*.*.*.*
';
IF OBJECT_ID(N'tempdb..#tb_request') IS NOT NULL
DROP TABLE #tb_request;
CREATE TABLE #tb_request(
id int IDENTITY
PRIMARY KEY,
server_name sysname,
database_name sysname,
schema_name sysname,
object_name sysname,
UNIQUE(
server_name, database_name, schema_name, object_name
)
WITH(
IGNORE_DUP_KEY = ON
)
);
WITH
DATA AS(
SELECT
server_name = PARSENAME(T.c.value('r[1]/text()[1]', 'sysname'), 1),
database_name = PARSENAME(T.c.value('r[2]/text()[1]