Wednesday, 28 November 2012

Get All Dependent SPs and Tables

Get All Dependent SPs and Tables


SELECT DISTINCT SysObjects.Name 'Table Name', Procedures.Name 'Stored Procedure'
FROM SysObjects
JOIN (SysObjects Procedures
JOIN SysDepends
ON Procedures.Id = SysDepends.Id)
ON SysDepends.DepId = SysObjects.Id
WHERE SysObjects.XType = 'U'
AND Procedures.XType = 'P'
-- AND sysobjects.name = 'EFSFilterData' --Filter Criteria goes here
GROUP BY SysObjects.Name, SysObjects.Id, Procedures.Name
ORDER BY SysObjects.Name Asc
 
--DB

Get RowCount for all the tables

Get RowCount for all the tables

 
Here are few ways of getting the Rowcount of the tables in the database.

SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
1 ASC --DESC


---------------------
EXEC sp_spaceused 'tablename'
--------------------
CREATE PROCEDURE dbo.listTableRowCounts
AS
BEGIN
SET NOCOUNT ON

DECLARE @SQL VARCHAR(255)
SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
EXEC(@SQL)

CREATE TABLE #foo
(
tablename VARCHAR(255),
rc INT
)

INSERT #foo
EXEC sp_msForEachTable
'SELECT PARSENAME(''?'', 1),
COUNT(*) FROM ?'

SELECT tablename, rc
FROM #foo
ORDER BY rc DESC

DROP TABLE #foo
END
----------------------------
GO

CREATE PROCEDURE dbo.allTables_SpaceUsed
AS
BEGIN
SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

CREATE TABLE #t
(
id INT,
TableName VARCHAR(32),
NRows INT,
Reserved FLOAT,
TableSize FLOAT,
IndexSize FLOAT,
FreeSpace FLOAT
)

INSERT #t EXEC sp_msForEachTable 'SELECT
OBJECT_ID(PARSENAME(''?'',1)),
PARSENAME(''?'',1),
COUNT(*),0,0,0,0 FROM ?'

DECLARE @low INT

SELECT @low = [low] FROM master.dbo.spt_values
WHERE number = 1
AND type = 'E'

UPDATE #t SET Reserved = x.r, IndexSize = x.i FROM
(SELECT id, r = SUM(si.reserved), i = SUM(si.used)
FROM sysindexes si
WHERE si.indid IN (0, 1, 255)
GROUP BY id) x
WHERE x.id = #t.id

UPDATE #t SET TableSize = (SELECT SUM(si.dpages)
FROM sysindexes si
WHERE si.indid < 2
AND si.id = #t.id)

UPDATE #t SET TableSize = TableSize +
(SELECT COALESCE(SUM(used), 0)
FROM sysindexes si
WHERE si.indid = 255
AND si.id = #t.id)

UPDATE #t SET FreeSpace = Reserved - IndexSize

UPDATE #t SET IndexSize = IndexSize - TableSize

SELECT
tablename,
nrows,
Reserved = LTRIM(STR(
reserved * @low / 1024.,15,0) +
' ' + 'KB'),
DataSize = LTRIM(STR(
tablesize * @low / 1024.,15,0) +
' ' + 'KB'),
IndexSize = LTRIM(STR(
indexSize * @low / 1024.,15,0) +
' ' + 'KB'),
FreeSpace = LTRIM(STR(
freeSpace * @low / 1024.,15,0) +
' ' + 'KB')
FROM #t
ORDER BY 1

DROP TABLE #t
END
 
--DB

Execute All SQL Files in a Directory

Execute All SQL Files in a Directory


SCRIPT #1

-----------------------------------------------------------
FORFILES /S /M *.sql /C "cmd /C sqlcmd -S HYD2CFETDWDEV2 -d TaxWarehouse -b -i @file >> ..\Log1.txt"

SCRIPT #2

------------------------------------------------------------
SET DWServer = %1
for /r "D:\TaxWarehouse\Tables\" "%X" in (*.sql) do sqlcmd -S DWServer -d TaxWarehouse -b -i %X
--DB

Thursday, 15 November 2012

Filter Dimension for last 5 years data


Following MDX query can be used to filter the Dimension for displaying past 5 years data.

SELECT { } ON COLUMNS,

{

    ( [Fiscal Period].[Fiscal Month Name].[Fiscal Month Name].ALLMEMBERS

    * [Fiscal Period].[Fiscal Year Name].[Fiscal Year Name].ALLMEMBERS

    * [Fiscal Period].[Period].[Period].ALLMEMBERS

    )

} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( StrToMember("[Fiscal Period].[Fiscal Year Name].&["+ FORMAT(DATEADD("YYYY",-5,NOW()),"yyyy")+"]")

              : StrToMember("[Fiscal Period].[Fiscal Year Name].&["+ FORMAT(DATEADD("YYYY",1,NOW()), "yyyy")+"]")

              ) ON COLUMNS

         FROM [KarnakReportmart]

     ) CELL PROPERTIES VALUE







Dynamically generate current Year, Month or Date member with MDX

MDX can be extended with Visual Basic functions like the FORMAT() and NOW() functions to dynamically generate the current year, month or date member. For example, it can be used as part of a named set to generate current year budget and actuals. It can also be used as part of a filter in a Performance Point dashboard.
First, a little review of VB NOW() and FORMAT() function:
NOW()
The NOW() VB function returns the current system date and time. It is similar to the T-SQL GETDATE() function.
FORMAT()
The FORMAT() VB function returns a string formatted according to instructions contained in a format String expression.
Source: http://msdn.microsoft.com/en-us/library/59bz1f0h(v=vs.90).aspx
There is an extensive list of characters you can use to create your own date and time formats. You can see the complete list here: http://msdn.microsoft.com/en-us/library/73ctwf33(v=vs.90).aspx
For example, you can extract the year portion of your date in the following formats:
y: Displays the year number (0-9) without leading zeros. Use %y if this is the only character in your user-defined numeric format.
Example: FORMAT(NOW(), “y”)
Result: 1 (for any date in 2011)
yy: Displays the year in two-digit numeric format with a leading zero, if applicable.
Example: FORMAT(NOW(), “yy”)
Result: 11 (for any date in 2011)
yyy or yyyy: Displays the year in four-digit numeric format.
Example: FORMAT(NOW(), “yyy”) or FORMAT(NOW(),”yyyy”)
Result: 2011 (for any date in 2011)
FORMAT() and NOW() with MDX
The following MDX example uses the Adventure Works 2008R2 Analysis Services OLAP cube. Suppose we need to return all Internet Sales for the year 2011. The MDX script would look something like this:
SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2011]

This works just fine and will return the aggregated sales of 2011. (Note: the Adventure Works 2008R2 does not contain sample sales data for 2011, result will be an Empty dataset).
But what if instead of a specific year, you are asked to return the Internet Sales amount for the current year? You have 3 options:
  1. On January 1st at 12:01 AM every year you need to change your MDX script to the new current year.
    CONS: You might be passed out from celebrating New Year’s Eve and most probably you will not get to it until a few days out into the new current year.
  2. You could use some MDX functions like LasNonEmpty and LastChild to figure out the last amounts recorded by the most recent date.
    CONS: You may have forecast data or simply bad data with transactions occurring on dates out into the future. (Trust me, I’ve seen it)
  3. You can build a dynamic MDX script that will return the current year based on the system datetime.
    CONS: It may be too sexy for your cube.
The trick is to construct the Date dimension member using the STRTOMEMBER MDX function. The STRTOMEMBER MDX function stands for “String to Member” and as the name implies, it will convert a string to a dimension member. In this case, we want to generate the [Date].[Calendar Year].&[2011] dimension member from a string using the Year portion of the system datetime.
The dynamic string will look like this:
StrToMember(“[Date].[Calendar Year].&["+FORMAT(NOW(), "yyyy")+"]“)
Now we can use it in our MDX script:
SELECT [Measures].[Internet Sales Amount] ON COLUMNSFROM [Adventure Works]
WHERE StrToMember(“[Date].[Calendar Year].&["+Format(now(), "yyyy")+"]“)

The same applies if you want to select or filter data for the current month. In the following example I’m be using the Month level from the Calendar Hierarchy and specifying the month of July (7) for the year 2011:
SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Month].&[2011]&[7]
We can generate the current month member as follows:
SELECT [Measures].[Internet Sales Amount] ON COLUMNS FROM [Adventure Works] WHERE StrToMember(“[Date].[Calendar].[Month].&["+Format(now(), "yyyy")+"]&["+Format(now(), "M")+"]“)
Note that to specify the Month portion we use CAPITAL letter M because it is case-sensitive. Smallcase letter m stands for minute. The complete list of characters can be viewed here: http://msdn.microsoft.com/en-us/library/73ctwf33(v=vs.90).aspx

source: http://sqljoe.wordpress.com/2011/07/22/dynamically-generate-current-year-month-or-date-member-with-mdx/
 

Thursday, 8 November 2012

Lost SA Password for SQL Server


YOUR SA PASSWORD FOR SQL SERVER 2012 IS LOST. NOW WHAT?

Here’s the scenario:
You are a proud and a trusted DBA of your organization who is responsible for some important services running on SQL Servers in the production environment. To prevent any unauthorized access to your production environment, you have decided to perform the following steps that are kind of best practices to secure your company’s SQL Servers from any unauthorized access:

þ  You have removed any and all built-in administrators account from SQL Server logins.

þ  You have removed all users (except SA) that were part of SYSADMIN server role (Including any Windows Accounts and/or SQL Server logins)

þ  You have set the password of SA account to something extremely complex which would be hard for anyone to guess or remember.

þ  For day-to-day operations on SQL Server, you use your domain user account which has DBO permissions on user databases but doesn’t have SYSADMIN privileges on the system.

þ  You have not documented the SA password anywhere to prevent others from knowing the SA password. (Also, it’s not a good practice to document the password)

Since you have set the SA password to be complex and you have been using your domain account and not the SA account for all your daily database related activity on SQL Server, the unthinkable has happened “you forgot your SQL Server’s SA password”. You are the only person who knew the SA password in your team and now you don’t remember what it was and you need to make some server level configuration changes to your production SQL Server boxes.

What would you do now? Here are some of the options I think you may do:

1.       You will try logging in as SA with all possible passwords you have in your mind.

2.       You will look for the SA password on your computer hard-drive or in your emails (If you had stored it in some file which is a bad practice)

3.       Try to restore MASTER database from database backup. However, this will not help because you will run in to the same issue as you don’t remember the SA password.

4.       Rebuild Master database. This may not help as you will lose all system/server level configurations and settings including logins, permissions and any server level objects.

5.       Re-install SQL Server 2012 and attach all user databases. This may not work as you may experience same issues that you would experience with #4.

All your attempts to login in to the system using SA password have failed and now it is time for you to call up the world’s best product support services team “Microsoft PSS”

Here’s what you can do:

Many folks have asked me about this and today, I’d like to share with you a backdoor to SQL Server 2012 which would help you gain SYSADMIN access to your production SQL Servers. However, that would mean your Windows account will need to be a member of the local administrators group on Windows Servers where SQL Server 2012 Services are running.

SQL Server allows any member of Local Administrators group to connect to SQL Server with SYSADMIN privileges.

Steps to take control of your SQL Server 2012 as an SA:

1.       Start the SQL Server 2012 instance using single user mode from command prompt by launching  the command prompt as an administrator. (Note: You can also start SQL Server 2012 using minimal configuration which will also put SQL Server in single user mode)
2.       From the command prompt (Run as Administrator) type: SQLServr.Exe –m (or SQLServr.exe –f) and let the SQL Server 2012 database engine start. Make sure you do not close this command prompt window.

You can locate SQLServr.exe in the Binn folder of your environmental path. If you don’t have SQL Server 2012 Binn folder in your environmental path, you can always navigate to the Binn folder of your SQL Server 2012 machine. Usually the Binn folder is located @ C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn>

3.       Once SQL Server 2012 service has been started in single user mode or with minimal configuration, you can now open up another command line window as an administrator and use SQLCMD command from command prompt to connect to SQL Server 2012 instance:

SQLCMD –S

Example: SQLCMD –S “SALEEMHAKANI”

You will now be logged in to SQL Server. Keep in mind that you are now logged in as an Admin on SALEEMHAKANI SQL Server 2012 instance.

4.       Once you are logged into the SQL Server 2012 using SQLCMD from the command prompt, you have the option of creating a new account and granting it any server level permission.

Let’s create a new login in SQL Server 2012 called “Saleem_SQL” and then add this account to SysAdmin server role.
      To create a new login from the command prompt after performing Step 3:

1> CREATE LOGIN ‘’ with PASSWORD=’’
           2> GO

Example:

1>     CREATE LOGIN SQL_SALEEM WITH PASSWORD=’$@L649$@m’
           2>     GO

To Once the new login “SQL_SALEEM” has been created, now let’s add this login to System Admin server role on SQL Server 2012 instance.

To add an existing login to SYSADMIN server role, execute the following:

1>     SP_ADDSRVROLEMEMBER ‘’,’SYSADMIN’
           2>     GO

Example:

1>     SP_ADDSRVROLEMEMBER SQL_SALEEM,’SYSADMIN’
           2>     GO

 The above operation will take care of granting SYSADMIN privileges to “SQL_SALEEM” login.

5.       Once the above steps are successfully performed, the next step is to stop and start SQL Server services using regular startup options. (This time you will not need –f or –m)

6.       Log in to the SQL Server 2012 management studio or from the command prompt using “SQL_SALEEM” account and it’s respective password, you now have system admin access to your SQL Server 2012 instance. You may now reset the SA password and take control of your production SQL Server boxes.

Reference : Saleem Hakani
 

Tuesday, 9 October 2012

PowerPivot from Identical Structure Excel Files

Introduction


PowerPivot for Excel 2010 is a powerful data analysis add-in that will let you work with millions of records within the familiar Excel environment. To download the free add-in, go to the PowerPivot page on the Microsoft website.

You can use PowerPivot to create a pivot table from multiple Excel workbooks or worksheets, by using the Primary and Foreign keys to join the tables. For example, there could be a 'ProductID' field in an Orders table and a PriceList table.

Occasionally you might want to create a pivot table from files where you can't use keys to join the tables. Maybe you have workbooks for different years' sales data, or budget data from several departments.

In this example, we'll combine data from two Excel files. The files have different data, but an identical structure -- sales data for the East and West regions. In this case, we can't use a key to connect the tables; instead, we want to create one combined table from all the data. The following technique allows you to import more than a million records from Excel, despite the fact that one worksheet can only contain up to 1,048,576 rows.

Thanks to Excel MVP, Kirill Lapin, for sharing this very helpful tip with us. You can see more of Kirill's work in the Contextures blog posts on Combining Data from Two Excel Files in a Pivot Table.

Create a Connection in the Workbook


The key to combining data from identical files is to start by creating a workbook connection, before you launch PowerPivot.

  • On the Excel Ribbon's Data tab, click Connections.
    Connections
  • In the Workbook Connections window, click Add
  • At the bottom of the Existing Connections window, click Browse for More.
    Browse for More
  • Navigate to the folder where your files are located. In this example the files are in the C:\_TEST folder
  • Select one of the files that you want to import -- EastSales.xlsx in this example -- and click Open.
    Select Data Source
  • Select a table to import, and click OK.
    Select Table to Import
  • The new connection appears in the Workbook Connections window.
    New Connection

Combine the Data in PowerPivot


  • Close the Workbook Connections window, and on the Ribbon, click the PowerPivot tab.
  • Click PowerPivot Window, to launch the PowerPivot add-in.
    Launch PowerPivot

Note: I'm using Windows XP, so the PowerPivot window has a menu bar in the screen shots below. If you're using Vista or Windows 7, you'll see a Ribbon instead.

  • On the Table menu, click Existing Connections, or, on the Ribbon, click Design, then Existing Connections.
    Existing Connections
  • At the bottom of the Existing Connections window, under Workbook Connections, click on the connection that you added, and click Open.
    Open Workbook Connection
  • In the Table Import Wizard, click Next, then select the table, and click Finish
    Table Import Wizard
  • After the data is successfully imported, click Close.
    Table Import Successful

Change the SQL Statement


Now that the first table has been imported, you can change its properties, to combine it with data from the second table.

  • On the Table menu, click Table Properties, or on the Ribbon, click the Design tab, then click Table Properties.
    Table Properties
  • From the Switch To drop down list, select Query Editor.
    Select Query Editor
  • Edit the SQL statement, to create a union query, combining the two tables. In this example, the SQL statement is:



    SELECT [EastSales$].* FROM [EastSales$]
    UNION ALL
    SELECT * FROM `C:\_TEST\WestSales.xlsx`.[WestSales$]
  • After you change the SQL statement, click the Validate button, to verify that the statement is correct, then click Save.
    Validate SQL

Note: The SQL query string can also be edited in the Excel workbook connection window, but there's no Validate feature there:

  • On the Excel Ribbon, click the Data tab, and click Connections
  • Select the connection you want to edit, and click Properties.
  • On the Definition tab, edit the SQL string in the Command text box.

Create the Pivot Table


Now that the data from the two files are combined, you can create a pivot table from the combined data.

  • On the Toolbar, click the Create a PivotTable button, or on the Ribbon, click the Home tab, then click PivotTable.
    Create PivotTable
  • Select a location for the pivot table, and click OK.
    Select PivotTable Location
  • In the PowerPivot Field List, add fields to the pivot table layout, to see a summary of the combined data.
    Add Pivot Table fields

Here's the pivot table that was created from the combined data, with columns for the East and West regions. The Report Layout is Tabular, and Number format is used, with thousands separator and zero decimals.

Completed Pivot Table

reference:
http://www.contextures.com/PowerPivot-Identical-Excel-Files.html