March 15, 2012 at 11:05 am
Hi
I am trying to do a describe (list column names) for a table. I have to use the openquery syntax in order to get the table name but i keep getting errors.
i have tried
select * from openquery(rm_linked, exec sp_help property)
openquery(rm_linked, exec; sp_help propert)
and a host of other combinations but still i get nothing
how can i accomplish this
March 15, 2012 at 2:51 pm
Hi, i think i see what you trying to do, have a look at this SP that i created to get schema information about any table in a database, just pass the table to the script
Create Procedure SCHEMA_INFO (@tblname varchar(255))
as
Begin
/*
Ref: Task [#821]
Name: identify_table_info.sql
Version: 1.0
Author: Glen Wass
Date Created: 19/10/2011
Description: script to display a table design
Impact:
Example exec: exec SCHEMA_INFO 'country'
Version History: DateVersionAuthorComments
---------------------------------------------------------------------------
19/10/2011|1.0| GW | Initial release
*/
-- find object id
declare @objid int,
@precscaletypes nvarchar(150)
select @objid=object_id from sys.all_objects where object_id = object_id(@tblname)
select @precscaletypes = N'tinyint,smallint,decimal,int,bigint,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,'
----reurn all collumns and respective data properties
select
'Column_name' = name,
'Type' = type_name(user_type_id),
'Computed' = case when ColumnProperty(object_id, name, 'IsComputed') = 0 then 'no' else 'yes' end,
'Length' = convert(int, max_length),
-- for prec/scale, only show for those types that have valid precision/scale
-- Search for type name + ',', because 'datetime' is actually a substring of 'datetime2' and 'datetimeoffset'
'Prec' = case when charindex(type_name(system_type_id) + ',', @precscaletypes) > 0
then convert(char(5),ColumnProperty(object_id, name, 'precision'))
else ' ' end,
'Scale' = case when charindex(type_name(system_type_id) + ',', @precscaletypes) > 0
then convert(char(5),OdbcScale(system_type_id,scale))
else ' ' end,
'Nullable' = case when is_nullable = 0 then 'no' else 'yes' end,
'TrimTrailingBlanks' = case ColumnProperty(object_id, name, 'UsesAnsiTrim')
when 1 then 'no'
when 0 then 'yes'
else '(n/a)' end,
'FixedLenNullInSource' = case
when type_name(system_type_id) not in ('varbinary','varchar','binary','char')
then '(n/a)'
when is_nullable = 0 then 'no' else 'yes' end,
'Collation' = collation_name
from sys.all_columns where object_id = @objid
End
***The first step is always the hardest *******
March 15, 2012 at 2:57 pm
Thank you
March 15, 2012 at 8:46 pm
Try this Addin: http://www.sqlxdetails.com
it does work with different db's on the same server (and the current db of course), but not (yet) over db link.
This example with procedure will work only in current database
(or if you create that procedure in remote db).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply