Tuesday, July 22, 2014
Monday, July 21, 2014
SMS Text Gateway Class Utilizing INotifyPropertyChanged
//
// SMS message Transport utilizing a webservice call
//
namespace messageTransport {
using System.Xml.Serialization;
/// <remarks/>
[System.SerializableAttribute()]
[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://messagetransport.webservice.com")]
[System.Xml.Serialization.XmlRootAttribute(Namespace="http://messagetransport.webservice.com", IsNullable=false)]
public partial class SMSMONotification : object, System.ComponentModel.INotifyPropertyChanged {
private string serviceVersionField;
private OcsSecurityType ocsSecurityField;
private smsEnrollRequest smsEnrollRequestField;
private smsUnEnrollRequest smsUnEnrollRequestField;
/// <remarks/>
public string ServiceVersion {
get {
return this.serviceVersionField;
}
set {
this.serviceVersionField = value;
this.RaisePropertyChanged("ServiceVersion");
}
}
/// <remarks/>
public OcsSecurityType OcsSecurity {
get {
return this.ocsSecurityField;
}
set {
this.ocsSecurityField = value;
this.RaisePropertyChanged("OcsSecurity");
}
}
/// <remarks/>
public smsEnrollRequest smsEnrollRequest {
get {
return this.smsEnrollRequestField;
}
set {
this.smsEnrollRequestField = value;
this.RaisePropertyChanged("smsEnrollRequest");
}
}
/// <remarks/>
public smsUnEnrollRequest smsUnEnrollRequest {
get {
return this.smsUnEnrollRequestField;
}
set {
this.smsUnEnrollRequestField = value;
this.RaisePropertyChanged("smsUnEnrollRequest");
}
}
public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;
protected void RaisePropertyChanged(string propertyName) {
System.ComponentModel.PropertyChangedEventHandler propertyChanged = this.PropertyChanged;
if ((propertyChanged != null)) {
propertyChanged(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName));
}
}
}
/// <remarks/>
[System.SerializableAttribute()]
[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://messagetransport.webservice.com")]
public partial class OcsSecurityType : object, System.ComponentModel.INotifyPropertyChanged {
private string userIdField;
private string passwordField;
private string x500IdField;
/// <remarks/>
public string UserId {
get {
return this.userIdField;
}
set {
this.userIdField = value;
this.RaisePropertyChanged("UserId");
}
}
/// <remarks/>
public string Password {
get {
return this.passwordField;
}
set {
this.passwordField = value;
this.RaisePropertyChanged("Password");
}
}
/// <remarks/>
public string X500Id {
get {
return this.x500IdField;
}
set {
this.x500IdField = value;
this.RaisePropertyChanged("X500Id");
}
}
public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;
protected void RaisePropertyChanged(string propertyName) {
System.ComponentModel.PropertyChangedEventHandler propertyChanged = this.PropertyChanged;
if ((propertyChanged != null)) {
propertyChanged(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName));
}
}
}
/// <remarks/>
[System.SerializableAttribute()]
[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://messagetransport.webservice.com")]
public partial class smsUnEnrollRequest : object, System.ComponentModel.INotifyPropertyChanged {
private string presentationNumberField;
private string shortCodeField;
private string phoneNumberField;
private string transactionIdField;
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string presentationNumber {
get {
return this.presentationNumberField;
}
set {
this.presentationNumberField = value;
this.RaisePropertyChanged("presentationNumber");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string shortCode {
get {
return this.shortCodeField;
}
set {
this.shortCodeField = value;
this.RaisePropertyChanged("shortCode");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string phoneNumber {
get {
return this.phoneNumberField;
}
set {
this.phoneNumberField = value;
this.RaisePropertyChanged("phoneNumber");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string transactionId {
get {
return this.transactionIdField;
}
set {
this.transactionIdField = value;
this.RaisePropertyChanged("transactionId");
}
}
public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;
protected void RaisePropertyChanged(string propertyName) {
System.ComponentModel.PropertyChangedEventHandler propertyChanged = this.PropertyChanged;
if ((propertyChanged != null)) {
propertyChanged(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName));
}
}
}
/// <remarks/>
[System.SerializableAttribute()]
[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://messagetransport.webservice.com")]
public partial class KeyValue : object, System.ComponentModel.INotifyPropertyChanged {
private string keyField;
private string valueField;
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string key {
get {
return this.keyField;
}
set {
this.keyField = value;
this.RaisePropertyChanged("key");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string value {
get {
return this.valueField;
}
set {
this.valueField = value;
this.RaisePropertyChanged("value");
}
}
public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;
protected void RaisePropertyChanged(string propertyName) {
System.ComponentModel.PropertyChangedEventHandler propertyChanged = this.PropertyChanged;
if ((propertyChanged != null)) {
propertyChanged(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName));
}
}
}
/// <remarks/>
[System.SerializableAttribute()]
[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://messagetransport.webservice.com")]
public partial class smsEnrollRequest : object, System.ComponentModel.INotifyPropertyChanged {
private KeyValue[] keyValueField;
private string presentationNumberField;
private requestType requestTypeField;
private string shortCodeField;
private string phoneNumberField;
private string messageField;
private string transactionIdField;
/// <remarks/>
[System.Xml.Serialization.XmlElementAttribute("keyValue")]
public KeyValue[] keyValue {
get {
return this.keyValueField;
}
set {
this.keyValueField = value;
this.RaisePropertyChanged("keyValue");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string presentationNumber {
get {
return this.presentationNumberField;
}
set {
this.presentationNumberField = value;
this.RaisePropertyChanged("presentationNumber");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public requestType requestType {
get {
return this.requestTypeField;
}
set {
this.requestTypeField = value;
this.RaisePropertyChanged("requestType");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string shortCode {
get {
return this.shortCodeField;
}
set {
this.shortCodeField = value;
this.RaisePropertyChanged("shortCode");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string phoneNumber {
get {
return this.phoneNumberField;
}
set {
this.phoneNumberField = value;
this.RaisePropertyChanged("phoneNumber");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string message {
get {
return this.messageField;
}
set {
this.messageField = value;
this.RaisePropertyChanged("message");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string transactionId {
get {
return this.transactionIdField;
}
set {
this.transactionIdField = value;
this.RaisePropertyChanged("transactionId");
}
}
public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;
protected void RaisePropertyChanged(string propertyName) {
System.ComponentModel.PropertyChangedEventHandler propertyChanged = this.PropertyChanged;
if ((propertyChanged != null)) {
propertyChanged(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName));
}
}
}
/// <remarks/>
[System.SerializableAttribute()]
[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://messagetransport.webservice.com")]
public enum requestType {
/// <remarks/>
ADD,
/// <remarks/>
UPDATE,
}
}
// SMS message Transport utilizing a webservice call
//
namespace messageTransport {
using System.Xml.Serialization;
/// <remarks/>
[System.SerializableAttribute()]
[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://messagetransport.webservice.com")]
[System.Xml.Serialization.XmlRootAttribute(Namespace="http://messagetransport.webservice.com", IsNullable=false)]
public partial class SMSMONotification : object, System.ComponentModel.INotifyPropertyChanged {
private string serviceVersionField;
private OcsSecurityType ocsSecurityField;
private smsEnrollRequest smsEnrollRequestField;
private smsUnEnrollRequest smsUnEnrollRequestField;
/// <remarks/>
public string ServiceVersion {
get {
return this.serviceVersionField;
}
set {
this.serviceVersionField = value;
this.RaisePropertyChanged("ServiceVersion");
}
}
/// <remarks/>
public OcsSecurityType OcsSecurity {
get {
return this.ocsSecurityField;
}
set {
this.ocsSecurityField = value;
this.RaisePropertyChanged("OcsSecurity");
}
}
/// <remarks/>
public smsEnrollRequest smsEnrollRequest {
get {
return this.smsEnrollRequestField;
}
set {
this.smsEnrollRequestField = value;
this.RaisePropertyChanged("smsEnrollRequest");
}
}
/// <remarks/>
public smsUnEnrollRequest smsUnEnrollRequest {
get {
return this.smsUnEnrollRequestField;
}
set {
this.smsUnEnrollRequestField = value;
this.RaisePropertyChanged("smsUnEnrollRequest");
}
}
public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;
protected void RaisePropertyChanged(string propertyName) {
System.ComponentModel.PropertyChangedEventHandler propertyChanged = this.PropertyChanged;
if ((propertyChanged != null)) {
propertyChanged(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName));
}
}
}
/// <remarks/>
[System.SerializableAttribute()]
[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://messagetransport.webservice.com")]
public partial class OcsSecurityType : object, System.ComponentModel.INotifyPropertyChanged {
private string userIdField;
private string passwordField;
private string x500IdField;
/// <remarks/>
public string UserId {
get {
return this.userIdField;
}
set {
this.userIdField = value;
this.RaisePropertyChanged("UserId");
}
}
/// <remarks/>
public string Password {
get {
return this.passwordField;
}
set {
this.passwordField = value;
this.RaisePropertyChanged("Password");
}
}
/// <remarks/>
public string X500Id {
get {
return this.x500IdField;
}
set {
this.x500IdField = value;
this.RaisePropertyChanged("X500Id");
}
}
public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;
protected void RaisePropertyChanged(string propertyName) {
System.ComponentModel.PropertyChangedEventHandler propertyChanged = this.PropertyChanged;
if ((propertyChanged != null)) {
propertyChanged(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName));
}
}
}
/// <remarks/>
[System.SerializableAttribute()]
[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://messagetransport.webservice.com")]
public partial class smsUnEnrollRequest : object, System.ComponentModel.INotifyPropertyChanged {
private string presentationNumberField;
private string shortCodeField;
private string phoneNumberField;
private string transactionIdField;
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string presentationNumber {
get {
return this.presentationNumberField;
}
set {
this.presentationNumberField = value;
this.RaisePropertyChanged("presentationNumber");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string shortCode {
get {
return this.shortCodeField;
}
set {
this.shortCodeField = value;
this.RaisePropertyChanged("shortCode");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string phoneNumber {
get {
return this.phoneNumberField;
}
set {
this.phoneNumberField = value;
this.RaisePropertyChanged("phoneNumber");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string transactionId {
get {
return this.transactionIdField;
}
set {
this.transactionIdField = value;
this.RaisePropertyChanged("transactionId");
}
}
public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;
protected void RaisePropertyChanged(string propertyName) {
System.ComponentModel.PropertyChangedEventHandler propertyChanged = this.PropertyChanged;
if ((propertyChanged != null)) {
propertyChanged(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName));
}
}
}
/// <remarks/>
[System.SerializableAttribute()]
[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://messagetransport.webservice.com")]
public partial class KeyValue : object, System.ComponentModel.INotifyPropertyChanged {
private string keyField;
private string valueField;
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string key {
get {
return this.keyField;
}
set {
this.keyField = value;
this.RaisePropertyChanged("key");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string value {
get {
return this.valueField;
}
set {
this.valueField = value;
this.RaisePropertyChanged("value");
}
}
public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;
protected void RaisePropertyChanged(string propertyName) {
System.ComponentModel.PropertyChangedEventHandler propertyChanged = this.PropertyChanged;
if ((propertyChanged != null)) {
propertyChanged(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName));
}
}
}
/// <remarks/>
[System.SerializableAttribute()]
[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://messagetransport.webservice.com")]
public partial class smsEnrollRequest : object, System.ComponentModel.INotifyPropertyChanged {
private KeyValue[] keyValueField;
private string presentationNumberField;
private requestType requestTypeField;
private string shortCodeField;
private string phoneNumberField;
private string messageField;
private string transactionIdField;
/// <remarks/>
[System.Xml.Serialization.XmlElementAttribute("keyValue")]
public KeyValue[] keyValue {
get {
return this.keyValueField;
}
set {
this.keyValueField = value;
this.RaisePropertyChanged("keyValue");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string presentationNumber {
get {
return this.presentationNumberField;
}
set {
this.presentationNumberField = value;
this.RaisePropertyChanged("presentationNumber");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public requestType requestType {
get {
return this.requestTypeField;
}
set {
this.requestTypeField = value;
this.RaisePropertyChanged("requestType");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string shortCode {
get {
return this.shortCodeField;
}
set {
this.shortCodeField = value;
this.RaisePropertyChanged("shortCode");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string phoneNumber {
get {
return this.phoneNumberField;
}
set {
this.phoneNumberField = value;
this.RaisePropertyChanged("phoneNumber");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string message {
get {
return this.messageField;
}
set {
this.messageField = value;
this.RaisePropertyChanged("message");
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string transactionId {
get {
return this.transactionIdField;
}
set {
this.transactionIdField = value;
this.RaisePropertyChanged("transactionId");
}
}
public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;
protected void RaisePropertyChanged(string propertyName) {
System.ComponentModel.PropertyChangedEventHandler propertyChanged = this.PropertyChanged;
if ((propertyChanged != null)) {
propertyChanged(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName));
}
}
}
/// <remarks/>
[System.SerializableAttribute()]
[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://messagetransport.webservice.com")]
public enum requestType {
/// <remarks/>
ADD,
/// <remarks/>
UPDATE,
}
}
C# AspNet Membership IsInRole - Save Roles To Session
public static bool IsInRole( string username, string rolename)
{
string UserRolesCheck = "UserRolesCheck" + username; if (HttpContext.Current.Session[UserRolesCheck] == null)
{ if (HttpContext.Current.Session[UserRolesCheck] == null) { string[] userRoles = System.Web.Security.Roles.GetRolesForUser(username); HttpContext.Current.Session[UserRolesCheck] = string.Join(",", userRoles); } } if (HttpContext.Current.Session[UserRolesCheck] != null) { return ((String)HttpContext.Current.Session[UserRolesCheck]).Contains(rolename) ? true : false; } return false; }
// The following Method is used for verifying roles of current user.
The contextual user must be authenticated.
public static bool IsInRole(string rolename) { if (HttpContext.Current.Session["UserRoles"] == null) { string[] userRoles = System.Web.Security.Roles.GetRolesForUser(System.Web.Security.Membership.GetUser().UserName); HttpContext.Current.Session["UserRoles"] = string.Join(",", userRoles); if (HttpContext.Current.Session["UserRolesCheck"] != null) HttpContext.Current.Session["UserRolesCheck"] = null; } if (HttpContext.Current.Session["UserRoles"] != null) { return ((String)HttpContext.Current.Session["UserRoles"]).Contains(rolename) ? true : false; } return false; }
{
string UserRolesCheck = "UserRolesCheck" + username; if (HttpContext.Current.Session[UserRolesCheck] == null)
{ if (HttpContext.Current.Session[UserRolesCheck] == null) { string[] userRoles = System.Web.Security.Roles.GetRolesForUser(username); HttpContext.Current.Session[UserRolesCheck] = string.Join(",", userRoles); } } if (HttpContext.Current.Session[UserRolesCheck] != null) { return ((String)HttpContext.Current.Session[UserRolesCheck]).Contains(rolename) ? true : false; } return false; }
// The following Method is used for verifying roles of current user.
The contextual user must be authenticated.
public static bool IsInRole(string rolename) { if (HttpContext.Current.Session["UserRoles"] == null) { string[] userRoles = System.Web.Security.Roles.GetRolesForUser(System.Web.Security.Membership.GetUser().UserName); HttpContext.Current.Session["UserRoles"] = string.Join(",", userRoles); if (HttpContext.Current.Session["UserRolesCheck"] != null) HttpContext.Current.Session["UserRolesCheck"] = null; } if (HttpContext.Current.Session["UserRoles"] != null) { return ((String)HttpContext.Current.Session["UserRoles"]).Contains(rolename) ? true : false; } return false; }
SQL Function fnSplitIn2Rows
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: tCarter
-- Create date: 4/14/2014
-- Description:
-- usage:
/* 1. (select * from fnSplitIn2Rows(@listItems,'','')
2. IF @listItems IS NOT NULL
BEGIN
....
inner join table1 t1 with (nolock) on t2.id = t1.id
inner join (select * from fnSplitIn2Rows(@listItems,'','')) UFNOUTPUT
on t1.id = UFNOUTPUT.itemvalue
END
ELSE
BEGIN
....
inner join table1 t1 with (nolock) on t2.id = t1.id
END
*/
-- =============================================
CREATE FUNCTION [dbo].[fSplitIn2Rows]
(
@PInStrSource varchar(8000) = NULL,
@pInChrSeparator char(1) = '|'
)
RETURNS
@ARRAY TABLE (ItemValue VARCHAR(1000))
AS
BEGIN
DECLARE @CurrentStr varchar(2000)
DECLARE @ItemStr varchar(200)
SET @CurrentStr = @PInStrSource
WHILE Datalength(@CurrentStr) > 0
BEGIN
IF CHARINDEX(@pInChrSeparator, @CurrentStr,1) > 0
BEGIN
SET @ItemStr = SUBSTRING (@CurrentStr, 1, CHARINDEX(@pInChrSeparator, @CurrentStr,1) - 1)
SET @CurrentStr = SUBSTRING (@CurrentStr, CHARINDEX(@pInChrSeparator, @CurrentStr,1) + 1, (Datalength(@CurrentStr) - CHARINDEX(@pInChrSeparator, @CurrentStr,1) + 1))
INSERT @ARRAY (ItemValue) VALUES (ltrim(@ItemStr))
END
ELSE
BEGIN
INSERT @ARRAY (ItemValue) VALUES (ltrim(@CurrentStr))
BREAK;
END
END
RETURN
END
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: tCarter
-- Create date: 4/14/2014
-- Description:
-- usage:
/* 1. (select * from fnSplitIn2Rows(@listItems,'','')
2. IF @listItems IS NOT NULL
BEGIN
....
inner join table1 t1 with (nolock) on t2.id = t1.id
inner join (select * from fnSplitIn2Rows(@listItems,'','')) UFNOUTPUT
on t1.id = UFNOUTPUT.itemvalue
END
ELSE
BEGIN
....
inner join table1 t1 with (nolock) on t2.id = t1.id
END
*/
-- =============================================
CREATE FUNCTION [dbo].[fSplitIn2Rows]
(
@PInStrSource varchar(8000) = NULL,
@pInChrSeparator char(1) = '|'
)
RETURNS
@ARRAY TABLE (ItemValue VARCHAR(1000))
AS
BEGIN
DECLARE @CurrentStr varchar(2000)
DECLARE @ItemStr varchar(200)
SET @CurrentStr = @PInStrSource
WHILE Datalength(@CurrentStr) > 0
BEGIN
IF CHARINDEX(@pInChrSeparator, @CurrentStr,1) > 0
BEGIN
SET @ItemStr = SUBSTRING (@CurrentStr, 1, CHARINDEX(@pInChrSeparator, @CurrentStr,1) - 1)
SET @CurrentStr = SUBSTRING (@CurrentStr, CHARINDEX(@pInChrSeparator, @CurrentStr,1) + 1, (Datalength(@CurrentStr) - CHARINDEX(@pInChrSeparator, @CurrentStr,1) + 1))
INSERT @ARRAY (ItemValue) VALUES (ltrim(@ItemStr))
END
ELSE
BEGIN
INSERT @ARRAY (ItemValue) VALUES (ltrim(@CurrentStr))
BREAK;
END
END
RETURN
END
Wednesday, July 9, 2014
SQL FUNCTION - GetAllTablesRowCounts( )
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: tcarter
-- Create date: 7/9/2014
-- Description: ALL TABLES SIZES
-- =============================================
CREATE FUNCTION [dbo].[GetAllTablesRowCounts]
(
@DBNAME VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
SELECT TOP 1000 OBJECT_NAME(object_id)tablename,* from sys.partitions where object_id>100 and index_id=1 order by rows desc
)
GO
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: tcarter
-- Create date: 7/9/2014
-- Description: ALL TABLES SIZES
-- =============================================
CREATE FUNCTION [dbo].[GetAllTablesRowCounts]
(
@DBNAME VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
SELECT TOP 1000 OBJECT_NAME(object_id)tablename,* from sys.partitions where object_id>100 and index_id=1 order by rows desc
)
GO
SQL Function - GetAllTablesByteSize( )
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: tcarter
-- Create date: 7/9/2014
-- Description: ALL TABLES SIZES
-- =============================================
CREATE FUNCTION GetAllTablesByteSize
(
@DBNAME VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
SELECT TOP 1000
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
UsedSpaceKB desc, t.Name
)
GO
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: tcarter
-- Create date: 7/9/2014
-- Description: ALL TABLES SIZES
-- =============================================
CREATE FUNCTION GetAllTablesByteSize
(
@DBNAME VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
SELECT TOP 1000
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
UsedSpaceKB desc, t.Name
)
GO
Subscribe to:
Posts (Atom)