Moving Indexes with Powershell and SMO

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Zach Mattson » Moving Indexes with Powershell and SMO

14 posts, Page 2 of 2

««1 2

Moving Indexes with Powershell and SMO

Rate Topic

Display Mode

Topic Options

Author

Message

RichB

Posted Friday, October 01, 2010 5:00 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 5:24 AM
Points: 1,028, Visits: 759

Helpful indeed.

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

Post #996532

katesl

Posted Friday, October 01, 2010 5:24 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 23, 2013 6:08 PM
Points: 60, Visits: 406

>>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.."

Post #996547

matt.birchall-942349

Posted Monday, October 04, 2010 6:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 27, 2013 1:40 PM
Points: 1, Visits: 8

RNNR ?

Post #997509

Kendal Van Dyke

Posted Monday, October 04, 2010 7:29 AM

Old Hand

Group: General Forum Members
Last Login: Friday, June 07, 2013 4:23 PM
Points: 361, Visits: 783

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/

Post #997544

« Prev Topic | Next Topic »

14 posts, Page 2 of 2

««1 2

Permissions