Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Moving Indexes with Powershell and SMO Expand / Collapse
Author
Message
Posted Friday, October 1, 2010 5:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:05 AM
Points: 1,070, Visits: 913
Helpful indeed.

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



Post #996532
Posted Friday, October 1, 2010 5:24 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 12:07 PM
Points: 63, Visits: 472
>>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
Posted Monday, October 4, 2010 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 19, 2014 3:47 AM
Points: 1, Visits: 19
RNNR ?
Post #997509
Posted Monday, October 4, 2010 7:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:35 PM
Points: 362, Visits: 897
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 »

Add to briefcase ««12

Permissions Expand / Collapse