SERVERPROPERTY of Sql Sever

SELECT SERVERPROPERTY('productversion')

SELECT SERVERPROPERTY ('productlevel')

SELECT SERVERPROPERTY ('edition')

SELECT SERVERPROPERTY('ServerName')

SELECT SERVERPROPERTY ('ProcessID')

SELECT SERVERPROPERTY ('NumLicenses')

SELECT SERVERPROPERTY('MachineName')

SELECT SERVERPROPERTY ('LicenseType')

SELECT SERVERPROPERTY ('IsSyncWithBackup')

SELECT SERVERPROPERTY('IsSingleUser')

SELECT SERVERPROPERTY ('IsIntegratedSecurityOnly')

SELECT SERVERPROPERTY ('IsFullTextInstalled')

SELECT SERVERPROPERTY('IsClustered')

SELECT SERVERPROPERTY ('InstanceName')

SELECT SERVERPROPERTY ('Engine Edition')

SELECT SERVERPROPERTY('Collation')

Data Base Modified Time

You can use this to find the last modified date for a stored procedure:

select name, create_date, modify_date
from sys.procedures
where name = 'sp_MyStoredProcedure'

You can use this to find the last modified date for a table:

select name, create_date, modify_date 
from sys.tables
where name = 'MyTable'

To find the last modified date and other info for other objects, you can query sys.objects .http://msdn.microsoft.com/en-us/library/ms190324.aspx contains a full list of types you can search for.

select top 10 * 
from sys.objects 
where type in ('p', 'u')

How to find out the tables and columns having particular data in SQL?

How to find out the tables and columns that hold the particular data

 In some of the scenario a developer have to check is there any particular value is stored in the any of the table in the entire db. It is very hard to find out the column and table that holds the particular data, by calling select query for each and every table in the database.

Following store procedure will search the particular data that we are given as the paramaeter to stored procedure, in the all tables and coloumns in the entire db and return the results. If we want to perfect match of the search key given the second parameter as 1 else 0.

StoredProcedure for search a keyword in the entire database.

CREATE PROCEDURE FindMyData_String
    @DataToFind NVARCHAR(4000),
    @ExactMatch BIT = 0
AS
SET NOCOUNT ON

DECLARE @Temp TABLE(RowId INT IDENTITY(1,1),
SchemaName sysname,
TableName sysname,
ColumnName SysName,
DataType VARCHAR(100), DataFound BIT)

INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
FROM    Information_Schema.Columns AS C
        INNER Join Information_Schema.Tables AS T
            ON C.Table_Name = T.Table_Name
    AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE   Table_Type = 'Base Table'
        And Data_Type In
        ('ntext','text','nvarchar','nchar','varchar','char')

DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)

SELECT  @SQLTemplate = CASE WHEN @ExactMatch = 1
    THEN 'If Exists(Select *
                  From   ReplaceTableName
                  Where  Convert(nVarChar(4000), [ReplaceColumnName])
                               = ''' + @DataToFind + '''
                  )
             Set @DataExists = 1
         Else
             Set @DataExists = 0'
    ELSE 'If Exists(Select *
                  From   ReplaceTableName
                  Where  Convert(nVarChar(4000), [ReplaceColumnName])
                               Like ''%' + @DataToFind + '%''
                  )
             Set @DataExists = 1
         Else
             Set @DataExists = 0'
    END,
    @PARAMETERS = '@DataExists Bit OUTPUT',
    @i = 1

SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp

WHILE @i <= @MAX
BEGIN
    SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName',
    QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)),
    'ReplaceColumnName', ColumnName)
    FROM    @Temp
    WHERE   RowId = @i

    PRINT @SQL
    EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

    IF @DataExists =1
        UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

    SET @i = @i + 1
END

SELECT  SchemaName,TableName, ColumnName
FROM    @Temp
WHERE   DataFound = 1
GO

How to get size of the database in SQL / SQL Azure?

Some of the case database exceeds the db size and got error while trying to insert new row into the db. Most of the case this will happen after some years of using database. So we cannot identify the issue very quickly and may be our application need to shut down for some days. To monitoring this issue we can create a user interface (if we needed) which showing currently used size of the database.

Query for getting size of the database in SQL / SQL Azure

To get the table wise size we can use following query, which returns the name of the tables with size of the table.

select    
      sys.objects.name, sum(reserved_page_count) * 8.0 / 1024 as size_in_MB 
from    
      sys.dm_db_partition_stats, sys.objects 
where    
      sys.dm_db_partition_stats.object_id = sys.objects.object_id
group by sys.objects.name

To get the entire database used size we can use following query.

select
      sum(reserved_page_count) * 8.0 / 1024 as size_in_MB
from
      sys.dm_db_partition_stats
GO

Windows Phone 8 SDK Links and Resources

Windows Phone 8 SDK Download Links and Resources 
 

http://mobile.dzone.com/articles/windows-phone-8-sdk-download

      

 

 

  

31 days of windows 8 development course

http://mobile.dzone.com/category/tags/31-days-windows-8

Copy tables from one database to another in SQL Server

You can  use the Generate SQL Server Scripts Wizard to help guide the creation of SQL script's that can do the following:

• copy the table schema
• any constraints (identity, default values, etc)
• data within the table
• and many other options if needed

Good example workflow for SQL Server 2008 with screen shots shown here.

Right Click on Database >> Tasks >> Generate Scripts >>

This will pop up Generate SQL Server Scripts Wizards >> Click on Next >> Select Database >> This will bring up a screen that will suggest to Choose Script Option.

On Choose Script Option Screen of Script Wizard under section Table/View Options Look at Script Data row and Turn the Option to True.

The Next Screen will ask about object types. Select all the objects that are required in the generated script. Depending on the previous screen it will show few more screen requesting details about the objects required in script generation.

On the very last screen it will request output options. Select the desired output options of Script to file, Script to Clipboard or Script New Query Window.3

Clicking on Finish button will generate a review screen containing the required objects along with script generating data.

Clicking on Finish button one more time will generate the requested output.

Similarly, if you want to generate data for only one table, you can right click on the table and follow almost a similar wizard. I am sure this neat feature will help everybody who has been requesting for it for a long time.

Ref:-http://blog.sqlauthority.com/2009/07/29/sql-server-2008-copy-database-with-data-generate-t-sql-for-inserting-data-from-one-table-to-another-table/

How to implement SQL Bulk Copy in SQL Server

SqlBulkCopy for insert multiple rows of data into table in SQL Server

Sometimes we need to insert bulk amount of data into a table in SQL. We can insert bulk data to SQL in different methods. Here we are going to demonstrate how to insert large amount of data using SQL Bulk Copy method.

Example of SQL BulkCopy method

Here we are going to insert multiple rows of sample data into Employee table. First of all create a table in SQL named Employee. After that  create sample data for employee table and finally this multiple rows of employee data insert into table using SQL bulk copy method.

 

Create a Table in SQL 

CREATE TABLE Employee
(
ID INT,
EmpName varchar(200),
Department varchar(200),
DOB DateTime
);

SQLMulkCopy Example in SQL Server

 

C# Code to generate sample data and insertion using SQL Bulk Copy

private void loadDataBySQLBulkCopy()
        {
            DataTable dtTblEmployee = new DataTable();
            dtTblEmployee.Columns.Add("ID", typeof(Int32));
            dtTblEmployee.Columns.Add("EmpName", typeof(String));
            dtTblEmployee.Columns.Add("Department", typeof(String));
            dtTblEmployee.Columns.Add("DOB", typeof(DateTime));

            DataRow dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 1; //Bind Data to Columns
            dtrow["EmpName"] = "Wazeem";
            dtrow["Department"] = "Admin";
            dtrow["DOB"] = "1990-10-16 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 2; //Bind Data to Columns
            dtrow["EmpName"] = "Aslam";
            dtrow["Department"] = "HR";
            dtrow["DOB"] = "1987-05-16 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 3; //Bind Data to Columns
            dtrow["EmpName"] = "John";
            dtrow["Department"] = "Finance";
            dtrow["DOB"] = "1992-12-11 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 4; //Bind Data to Columns
            dtrow["EmpName"] = "Mishal";
            dtrow["Department"] = "Infra structure";
            dtrow["DOB"] = "1989-04-01 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            SqlBulkCopy bulkCopy = new SqlBulkCopy(
                "server=TEST;database=TEST;uid=test;password=test",
                SqlBulkCopyOptions.TableLock);
            bulkCopy.DestinationTableName = "dbo.Employee";
            bulkCopy.WriteToServer(dtTblEmployee);

        }

About these ads