Blog

My blog serves as a way for me to post solutions to problems I solve at work or during the long hours of freelance work.


SQL Server Number of Rows in all Tables in a Database

by Ezekiel 7. November 2014 06:33

 

SELECT 

t.NAMEASTableName,

i.nameasindexName, 

p.[Rows], 

sum(a.total_pages)asTotalPages, 

sum(a.used_pages)asUsedPages, 

sum(a.data_pages)asDataPages,

(sum(a.total_pages)* 8)/ 1024 asTotalSpaceMB, 

(sum(a.used_pages)* 8)/ 1024 asUsedSpaceMB, 

(sum(a.data_pages)* 8)/ 1024 asDataSpaceMB

FROM 

sys.tablest 

INNERJOIN 

sys.indexesiONt.OBJECT_ID=i.object_id 

INNERJOIN 

sys.partitionspONi.object_id=p.OBJECT_IDANDi.index_id=p.index_id 

INNERJOIN 

sys.allocation_unitsaONp.partition_id=a.container_id 

WHERE 

t.NAMENOTLIKE'dt%'AND 

i.OBJECT_ID> 255 AND 

i.index_id<= 1 

GROUPBY 

t.NAME,i.object_id,i.index_id,i.name,p.[Rows] 

ORDERBY 

object_name(i.object_id)

Tags: ,

SQL | SQL Server

Show All Indexes in a SQL Server Table

by Ezekiel 3. October 2014 06:19

 

select*fromsys.indexes 

whereobject_id=(selectobject_idfromsys.objectswherename='PayrollSchedule')

Tags:

SQL

Delete an Index from a SQL Server Database

by Ezekiel 2. July 2014 11:12

 

DROPINDEXtable.index_name

Tags: , ,

SQL | SQL Server

Show All Indexes in a SQL Server Database

by Ezekiel 2. July 2014 11:05

 

SELECTDB_NAME()AS DatabaseName

, sc.name ASSchema_Name 

, o.name AS Table_Name 

, i.name AS Index_Name 

, i.type_desc AS Index_Type 

FROMsys.indexes

INNERJOINsys.objects o ON i.object_id= o.object_id 

INNERJOINsys.schemas sc ON o.schema_id= sc.schema_id 

WHERE i.name ISNOTNULL 

AND o.type='U' 

ORDERBY o.name, i.type

Tags: , ,

SQL | SQL Server

SQL Alter Table Add Column with Default Value Update Table

by Ezekiel 6. February 2014 07:49

-- Adding New Column
ALTER TABLE TestTable
ADD SecondCol INT
GO


-- Updating it with Default
UPDATE TestTable
SET SecondCol = 0
GO


-- Alter
ALTER TABLE TestTable
ALTER COLUMN SecondCol INT NOT NULL
GO

Tags: , ,

SQL | SQL Server

How to do Top 1 in Oracle

by Ezekiel 15. August 2012 04:03

 

select fname from MyTbl where rownum = 1

Tags: , ,

SQL | Oracle

SQL Combine Multiple Records into One Record.

by Ezekiel 7. May 2012 04:06

 

DECLARE @List VARCHAR(MAX)

SELECT @List = COALESCE(@List + ' | ' , '') + s.Name
FROM table1 t
JOIN table2 t2 ON t2.Id = t.Id
WHERE t.Id = 1

SELECT @List

Tags: , ,

SQL | SQL Server

SQL Server Search all Tables for Value

by Ezekiel 9. February 2012 06:29


DECLARE @SearchStr nvarchar(100)
SET @SearchStr = 'SearchTerm'

    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
   
    BEGIN
        SET @ColumnName = ''
        SET @TableName =
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
           
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
   
            IF @ColumnName IS NOT NULL
           
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END   
    END

    SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

Tags: ,

SQL | SQL Server

Insert into Table Using a SQL Select Statement

by Ezekiel 5. June 2011 05:22

 

INSERT INTO table1 (column1, column2)
SELECT column3, column4 FROM table2

Tags: , ,

SQL | SQL Server

Breadcrumb Using LINQ to SQL, Generic Class and Recursive CTE

by Ezekiel 12. November 2010 16:23
[No text]

Tags: , , , ,

ASP.NET | C# | LINQ to SQL | SQL

How to Search Stored Procedures for Specific Database Table

by Ezekiel 18. June 2010 06:53

 

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%TableName%'
AND ROUTINE_TYPE='PROCEDURE'

Tags:

SQL Server

Add Column or Multiple Columns to Table in SQL Server Using SQL

by Ezekiel 1. May 2010 06:10

 

ALTER TABLE tableName
ADD columnName varchar(50)

 

ALTER TABLE tableName
ADD columnName1 char(1) DEFAULT 'N' NOT NULL,
columnName2 char(1) DEFAULT 'N' NOT NULL,
columnName3 char(1) DEFAULT 'N' NOT NULL,
columnName4 char(1) DEFAULT 'N' NOT NULL,
columnName5 char(1) DEFAULT 'N' NOT NULL

Tags: ,

SQL | SQL Server

How to Search all Stored Procedures in a Database

by Ezekiel 10. April 2010 06:43

 

SELECT O.NAME, S.DEFINITION, ISR.ROUTINE_CATALOG, ISR.ROUTINE_SCHEMA,
O.TYPE AS obj_TYPE_CODE, O.TYPE_DESC AS obj_TYPE_DESCRIPTION

FROM SYS.OBJECTS AS O
INNER JOIN    SYS.SQL_MODULES AS S ON O.OBJECT_ID = S.OBJECT_ID
LEFT JOIN    SYS.SYSOBJECTS AS SO ON O.OBJECT_ID = SO.id
LEFT JOIN    INFORMATION_SCHEMA.ROUTINES ISR ON SO.NAME = ISR.ROUTINE_NAME

WHERE S.DEFINITION LIKE '%JOIN%'AND S.DEFINITION LIKE '%ON%'
ORDER BY O.TYPE,O.NAME

Tags: ,

SQL | SQL Server

How to Update Column from a Column in the same Table

by Ezekiel 10. April 2010 06:39

 

UPDATE tablename
SET ID = ID2
WHERE ID = ID

Tags: ,

SQL | SQL Server

How to Add Primary Key Cluster Using SQL Script

by Ezekiel 10. April 2010 06:35

 

ALTER TABLE TABLENAME
ADD CONSTRAINT [PK_TABLENAME]
PRIMARY KEY CLUSTERED
(
    [ID] ASC,
    [ID2] ASC
)

Tags: ,

SQL | SQL Server

How to Restore SQL Server Database from .bak File

by Ezekiel 23. December 2009 05:12

 

1. Open SQL Server Management Studio on your local machine.
2. Right click the Databases folder. From the pop-up menu, select New Database.
3. Enter a database name, and then click Ok.
4. Right click the new database icon. From the pop-up menu, select Tasks -> Restore -> Database.
5. Select the From Device option, and then click the browse button.
6. Click Add and navigate to the appropriate file. Click Ok.
7. In the Restore Database window, select the checkbox next to your .bak file.
8. Switch to the Options page. Select the Overwrite the existing database checkbox.
9. Click Ok.

Tags: ,

SQL Server

How to Restore SQL Server Database from .mdf File

by Ezekiel 23. December 2009 04:54

 

1. Open SQL Server Management Studio on your local machine.
2. Right click the Databases folder. From the pop-up menu, select Attach.
3. Click Add and select the appropriate .mdf file. Click Ok, and then click Ok again.
4. Verify the contents of your database, which is now active on your local machine.

Tags: ,

SQL Server

Populate an ASP.NET DropDownList Control

by Ezekiel 4. November 2009 10:11

 

HTML -

<asp:DropDownList ID="ddlStatus" runat="server">
</asp:DropDownList>

 

C# -

// Clear all items in drop down
ddl.Items.Clear();

// Populate the status filter
ddl.DataTextField = "dataFieldToShowToPublic";
ddl.DataValueField = "dataFieldToPassToMethod";

// Call to your data layer
ddl.DataSource = sqlObject.GetDropDownData();
ddl.DataBind();
           
// Blank item at the top of the list
ddl.Items.Insert(0, new ListItem("--", ""));

 

Data Layer Method -

public DataTable GetDropDownData()
{
    SqlConnection conn = GetConnection();
    SqlCommand cmd = conn.CreateCommand();
    DataSet ds = new DataSet();
    SqlDataAdapter da = new SqlDataAdapter(cmd);

    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT * FROM TableName ORDER BY dataField";
    da.Fill(ds);

    conn.Close();
    return ds.Tables[0];
}

 

 

Tags: , , ,

ASP.NET | C# | SQL

SQL LEFT OUTER JOIN

by Ezekiel 27. October 2009 03:45

 

For a long time I had a problem understanding how to use a LEFT OUTER JOIN in my SQL statement.  A lot of explanations out there are hard to understand.  So a LEFT OUTER JOIN works by joining the left side of the equals sign to the right side.  If there isn't a record to tie it to will not effect your return. 

For example:

DECLARE @promotionKey varchar(100)
SET @promotionKey = 'PromotionName'

SELECT TOP 1 pl.promotionID  
   FROM Promotions p
   JOIN PromotionLocales pl ON p.promotionID = pl.promotionID
   JOIN PromotionCodes pc ON pl.promotionID = pc.promotionID
   LEFT OUTER JOIN PromotionCourseRequirements pcr ON pcr.promotionID = pl.promotionID
 
 WHERE p.promotionKey = @promotionKey

Here I'm looking for a record in table PromotionCourseRequirements which has the same promotionID as in table PromotionLocales.  If there isn't one it will not effect the returned records.

 

 

Tags:

SQL

RecentPosts