Truncated SQL Server procedures.

[Duslerke]

What:

Sqlectron truncating stored procedure code on SQL Server databases.

Why:

I did some digging through the Sqlectron front-end code, which pointed me to this DB Core repository.

The issue is where this query is getting its information from (lines 386-388) (see SO post):

sqlectron-db-core/src/adapters/sqlserver.ts

Lines 384 to 396 in 821da76

	async getRoutineCreateScript(routine: string): Promise<string[]> {
	const sql = `
	SELECT routine_definition
	FROM INFORMATION_SCHEMA.ROUTINES
	WHERE routine_name = '${routine}'
	`;
	const { data } = await this.driverExecuteSingleQuery<{ routine_definition: string }>({
	query: sql,
	});
	return data.map((row) => appendSemiColon(row.routine_definition));
	}

The INFORMATION_SCHEMA.ROUTINES is merely a View cobbled together from a bunch of sys objects, where the ROUTINE_DEFINITION is parsed into nvarchar(4000).

This can be verified by looking up the INFORMATION_SCHEMA.ROUTINES definition with: SELECT OBJECT_DEFINITION(OBJECT_ID('INFORMATION_SCHEMA.ROUTINES'));. Snippet from the INFORMATION_SCHEMA.ROUTINES definition:

CREATE VIEW INFORMATION_SCHEMA.ROUTINES
AS
SELECT
	-- ... view columns ...
	convert(nvarchar(4000),
		OBJECT_DEFINITION(o.object_id))	AS ROUTINE_DEFINITION,
       -- ... more view columns ...
FROM
	sys.objects$ o LEFT JOIN sys.parameters c 
-- the rest of the definition

Also see Microsoft's documentation [link].

Potential fix?

One of the ways to get full procedure code is:

SELECT definition FROM sys.sql_modules WHERE object_id=OBJECT_ID('procedure_name');

Or it could be fixed by mimicking the INFORMATION_SCHEMA.ROUTINES definition and using OBJECT_DEFINITION, but without any parsing to limited length string.

Notes:

• The proposed fixes may not be compatible with older SQL versions (version pre-2005). link1, link2.
• Related Sqlectron front-end issue link to issue.