SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving Indexes with Powershell and SMO


Moving Indexes with Powershell and SMO

Author
Message
RichB
RichB
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3048 Visits: 1065
Helpful indeed.

However, wouldn't it be even more helpful if there was an equivalent of sp_helptext for indexes and tables...



katesl
katesl
SSC-Addicted
SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)

Group: General Forum Members
Points: 407 Visits: 473
>>wouldn't it be helpful

It takes a few minutes to write exactly the helpful query you need on tables and indexes, no need to wish

Use this query to identify which columns in a table must have values supplied, and the dataypes of those columns,
also to see the defaults that will be supplied for columns when you rely on default value.

declare @tblname sysname
set @tblname =
'user‘
print @tblname
select left(sc.name,30) as columnName
-- + ','
, left(st.name,10) as datatype
, sc.max_length
, sc.is_identity
, sc.is_computed
, sc.is_nullable
, case when sdc.name is null then 0 else 1 end as has_default
, cast(left(isnull(sdc.definition,''),12) as varchar(12)) as defaultValue
,cast(sc.is_identity as int)+cast(sc.is_computed as int) + cast(sc.is_nullable as int) +
case when sdc.name is null then 0 else 1 end
as valueIsSupplied
from sys.columns sc
join sys.types st
on st.user_type_id = sc.user_type_id
left join sys.default_constraints sdc
on sc.object_id = sdc.parent_object_id
and sc.column_id = sdc.parent_column_id
where sc.object_id in (select object_id from sys.objects where type = 'u' and name = @tblname)
order by valueIsSupplied
, sc.column_id

_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
Recombinant
Recombinant
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 84
RNNR ?
Kendal Van Dyke
Kendal Van Dyke
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1662 Visits: 983
RichB (10/1/2010)
Helpful indeed.

However, wouldn't it be even more helpful if there was an equivalent of sp_helptext for indexes and tables...


This sounds a bit off topic from what the discussion was about. But in any case, check out sp_help for tables and Kim Tripp's sp_helpindex8 for indexes.

Kendal Van Dyke
http://kendalvandyke.blogspot.com/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search