https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database 

Is there an easy way to determine how much space on disk each table is consuming?

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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 
    t.Name

CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
DROP TABLE #counts

The output will be a list of tables and their row counts.

If you just want the total row count across the whole database, appending:

SELECT SUM(row_count) AS total_row_count FROM #counts

will get you a single value for the total number of rows in the whole database.

PROBLEM: I have been struggling with this error now for days now and have tried everything I know. I have an SQL statement that pulls data from several tables into another table. The field in question is a NTEXT field from a SQL 2000 database, which I now import into a SQL 2008 R2 table that is NVARCHAR(MAX) data type because I though the issue was the NTEXT data type. However the SSIS package that is just an OLE DB Source (with 1 field) into an Excel Destination is still giving me the Unicode and Non-Unicode Error!! Several rows of data are over 8000 characters in length.

ANSWER: After a lot of pain I finally came to the conclusion that Exporting to EXCEL is not possible so I turned to CSV. I used "Flat File Destination" object, pointed to a CSV that I had created with just the Headers. The Text Qualifier was set to double quotes. In the Columns section I set the Row delimiter to {CR}{LF} and the Column delimiter to Comma{,} because it is a CSV! The final part of the puzzle was to remove and double quotes, Carriage Returns and Line Feeds. I also had to convert the NTEXT field to VARCHAR(MAX) because REPLACE will not work with NTEXT. This is what I ended up with for the columns that had these "invalid characters".

REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(MAX),[MyNTEXTColumn]), CHAR(13),' '), '"', ''), CHAR(10),'') AS 'Corrected Output'

I replaced {CR} CHAR(13) with a space to that we could have it formatted well for the consumer. I hope this helps someone out one day.

To resolve the invalid chracter from FOR XML PATH clause

Background
I have a stored procedure which returns data as an XML node. This stored procedure uses temp table to populate data. The column names in the temp table contain spaces, i.e. [Create Date].

Error
"Column name Create Date contains an invalid XML identifier as required by FOR XML; ' '(0x0020) is the first character at fault."

Resolution
After removing spaces from the column names, this error was resolved.

Thanks to: http://www.codeproject.com/Tips/142869/Column-name-Create-Date-contains-an-invalid-XML-i

WITH  q AS
        (
        SELECT TOP 1 *
        FROM    mytable
        /* You may want to add ORDER BY here */
        )
DELETE
FROM    q

Note that

DELETE TOP (1)
FROM   mytable

will also work, but, as stated in the documentation:

The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order.

Therefore, it's better to use WITH and an ORDER BY clause, which will let you specify more exactly which row you consider to be the first.

The Actual Error for me was

Msg 7347, Level 16, State 1, Line 2

OLE DB provider 'SQLNCLI10' for linked server 'Common_Pts' returned data that does not match expected data length for column '[Common_Pts].[Pts].[dbo].[veRegulatoryInstrument].prefix'. The (maximum) expected data length is 4, while the returned data length is 5. 

After creating a linked server in ms sql express. and running the view I got the following error. to solve the problem run sp_refreshview command from the server that is hosting the view.

sp_refreshview [ @viewname = ] 'viewname' 

http://msdn.microsoft.com/en-us/library/ms187821.aspx

Replace Diameter with your own search.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Diameter%'
ORDER BY schema_name, table_name;

List ALL fields in a Database

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID 
ORDER BY schema_name, table_name;

Hope this helps.

I came across a challenge this week. Migrating Blob data from Oracle to SQL Server. I used MS Access for this since I could create an ODBC DSN to each and then have the best of ALL worlds. That is review the data, count the records and write very simple code. The link below is excellent and worked perfectly for what I needed for dealing with the Blobs.  

How To Read and Write BLOBs Using GetChunk and AppendChunk: http://support.microsoft.com/default.aspx?scid=kb;en-us;194975

The process I used for the Blobs was this:
1. Copy the data from Oracle to SQL exluding the Blobs Column.
2. Export the Blobs to File using the ID field as a key like (ID) & FileName. FileName was a column in my Oracle table so that was nice and pretty well needed so you can Identift the MIME (file type like XLS, DOC, PDF).
3. Finally Update the SQL Server table using File to Blob.

I had some other "things" in the code to deal with missing files from the Blob column but that was simple.
 

When attempting to Sort by different "MULTIPLE" Columns using a SQL Server Stored Procedure, this is the how it is done! 

USE AdventureWorks; 
SELECT   ContactID, 
         FirstName, 
         LastName, 
         Title = COALESCE(Title,'') 
FROM     Person.Contact 
WHERE    LEFT(FirstName,1) = 'M' 
ORDER BY CASE 
           WHEN LEFT(LastName,1) = 'A' THEN RANK() OVER(ORDER BY FirstName + LastName) 
           WHEN LEFT(LastName,1) = 'M' THEN RANK() OVER(ORDER BY LastName + ', ' + FirstName, Title) 
           WHEN LEFT(LastName,1) = 'U' THEN RANK() OVER(ORDER BY LastName + ', ' + FirstName DESC) 
           ELSE                             RANK() OVER(ORDER BY LastName DESC, FirstName DESC) 
         END 

Another example: This is passing in the Sort order via a Stored Procedure Parameter. In this case @SortOrder is a varchar(100).

SELECT ItemId, Prod, Size, TagNo, ProductId, Location, PONo
FROM vewInvTrxSummary
ORDER BY 
        CASE WHEN @SortOrder LIKE '%Product, Tag, Location, Size, PO%' 
			THEN RANK() OVER(ORDER BY Prod, TagNo, Location, Size, PONo)
                  WHEN @SortOrder LIKE '%Product, Size, Tag, Location, PO%' 
			THEN RANK() OVER(ORDER BY Prod, Size, TagNo, Location, PONo)
                  WHEN @SortOrder LIKE '%Size, Product, Tag, Location, PO%' 
			THEN RANK() OVER(ORDER BY Size, Prod, TagNo, Location, PONo)
                  WHEN @SortOrder LIKE '%Product, PO, Tag%'                        
			THEN RANK() OVER(ORDER BY Prod, PONo, TagNo)
                    ELSE RANK() OVER(ORDER BY Prod, TagNo, Location, Size, PONo) 
         END

If you are working with SQL Stored Procedures and Functions, it's simple to add the db_owner role to your own login. However, when you want to release your database to a production environment you really want to take away the db_owner role as it has the ability to add, edit and remove database objects in a production environment. The proper way of assigning permissions is to give the user\group in question db_reader and db_writer privilages ONLY and remove the db_owner role. You then need to GRANT the user\group EXECUTE permission on all the Stored Procedures.

To do this you may need to run the following script in order to generate the script so this may be done quickly rather than applying the permission one by one. Once run, simply copy the results to a new window and Execute all lines.

DECLARE @User varchar(50)
SELECT @User = '[domain\userid]'

SELECT 'GRANT EXEC ON ' + name + ' TO ' + @User
FROM  dbo.sysobjects
WHERE type IN ('P', 'FN')
AND category = 0
ORDER BY name

OR if you want to GRANT to PUBLIC

DECLARE @User varchar(50)
SELECT @User = 'PUBLIC'

SELECT 'GRANT EXEC ON ' + name + ' TO ' + @User
FROM  dbo.sysobjects
WHERE type IN ('P', 'FN')
AND category = 0
ORDER BY name

This link had more details regarding the objects you might need to query.
http://msdn.microsoft.com/en-us/library/ms190324.aspx