June 23, 2008 at 10:15 am
I am trying to read the schema information to spit out a bunch of EXEC statements (into a temp file) that provide Primary Key information as parameters. So, I should wind up with this:
EXEC spMyProc N'dbo.Table1',
N'PrimKeyField1', N'nVarChar(20)',
N'PrimKeyField2', N'int'
EXEC spMyProc N'dbo.Table1',
N'PrimKeyField1', N'nVarChar(20)',
N'PrimKeyField2', N'int',
N'PrimKeyField3', N'money'
...
I think that rather than a join I need some kind of subquery. I would read the Tables and for each table spit out the 'EXEC' line, then for each PK, I would spit out the PK line.
I don't need the details on the Schema tables (I have that) just the general statement. This is what I was working on but I realized I didn't quite get the syntax:
CREATE TABLE #MyTempTable
(pkey INT NOT NULL IDENTITY (1, 1),
ID INT ,
MyStatement NVARCHAR(4000))
INSERT INTO #MyTempTable (MyStatement)
Select 'EXEC dbo.spGenerateInsUpdate N''' + c.TABLE_SCHEMA + '.' + c.TABLE_NAME + ''','
From INFORMATION_SCHEMA.COLUMNS C
Select 'N''' + K.COLUMN_NAME + '' +', N''' + dbo.spFormatColumnDataType(K.cha)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
WHERE
K.Table_Schema=C.Table_Schema
And K.Table_Name=C.Table_Name
And K.Column_Name=C.Column_Name
And K.Constraint_Name Like 'PK_%'
Order By K.TABLE_SCHEMA, K.TABLE_NAME, K.ORDINAL_POSITION
Also, does anyone know if there is a function available that will read the Column data and return a formatted Data Type string (such as, "nVarChar(20)" or "money")? In the table, that info is in separate fields.
Thanks in advance,
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
June 24, 2008 at 10:56 am
This might be a start:
select
so.[name],
ac.[name],
st.[name],
st.prec,
st.scale
from
sys.all_columns ac
inner join sys.objects so on so.object_id = ac.object_id
inner join msdb.sys.systypes st on st.xtype = ac.system_type_id
where
so.type = 'U'
order by
so.[name],
ac.[name],
st.[name]
June 24, 2008 at 11:53 am
try this:
/*
1 use union, not join
2 Use INFORMATION_SCHEMA.TABLES
3 include columns for ordering, remove in final
4 put commas at beginning, NOT end
*/
Select CmdOut
From(Select 'EXEC dbo.spGenerateInsUpdate N''' + TABLE_SCHEMA + '.' + TABLE_NAME as CmdOut,
TABLE_SCHEMA, TABLE_NAME, 0 as ORDINAL_POSITION
From INFORMATION_SCHEMA.TABLES
UNION ALL
Select ' , N''' + K.COLUMN_NAME + '' +', N'''
+ dbo.spFormatColumnDataType(c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH) as CmdOut,
k.TABLE_SCHEMA, k.TABLE_NAME, k.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
INNER JOIN INFORMATION_SCHEMA.COLUMNS C
ON K.Table_Schema=C.Table_Schema
And K.Table_Name=C.Table_Name
And K.Column_Name=C.Column_Name
WHERE K.Constraint_Name Like 'PK_%') U
Order By U.TABLE_SCHEMA, U.TABLE_NAME, U.ORDINAL_POSITION
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 24, 2008 at 12:01 pm
Interesting to to see the two completely different approaches in action!
Thanks again.
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
June 24, 2008 at 12:11 pm
Glad we could help. Let us know how it works out.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply