Create generic insert, update, delete using xml-querying system tables
-
hi pipz,
i have read this article and it helped me a lot.. tnx
Querying System Tables
Regular Columnist : Raj Vasant
Posted: 10/20/2006
i have created a dynamic function that created at runtime a block of code(it is an insert statement) that can be used in different tables in our database because, you see i have been making insert, update and delete procedures for each modules for the past one year, actually it very tiring and time cosuming.. and i have come to think, "what if i will create a function that returns a varchar value and if called, will be executed". I will only pass parameters like the datable name, where i can get the columns of the table using syscolumns.name and other details.
i vae successfully created a function for insert, but as i review the code, (xml implementation)
...
WITH(
DesignationId int, SalaryPeriod int, DepartmentId int, PayrollPeriod varchar, Remarks varchar )
..
as your have notice the datatype for the column PayrollPeriod and Remarks is varchar, the problem is it does not specify the length , i need the length of the datatype varchar? how can i get it? it is stated that the default length of a varchar datatype is 50, but user-defined length for each column..
tnx..
Godbless
-
-
Something like...
SELECT so.name as TableName, sp.value AS description, sc.name as ColumnName, UPPER(st.name) as DataType, sc.length, CASE sc.isnullable WHEN 1 THEN NULL ELSE '' END AS isnullable, CASE WHEN scom.text IS NULL THEN '' ELSE scom.text END as text --, indexes.name
FROM syscolumns sc
JOIN sysobjects so ON sc.id = so.id
JOIN systypes st ON sc.xtype = st.xtype
LEFT JOIN sysproperties sp ON sc.id = sp.id AND sc.colid = sp.smallid
LEFT JOIN syscomments scom ON sc.id = scom.id AND sc.iscomputed = scom.colid
WHERE so.type = 'U'
ORDER BY so.name, sc.colorder
... Will also give you any computed column formulae.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply