Пример #1
0
        private static void CreateAnonDbInfosIfNoExists()
        {
            OleDbDataBaseReader reader = new OleDbDataBaseReader(ConfigurationManager.ConnectionStrings["IdentityDbOleDb"].ConnectionString);

            try
            {
                reader.LoadTables(AnonDbInfosTableName);
            }
            // if table {AnonDbInfosTableName} does not exist
            catch (OleDbException)
            {
                using (
                    SqlServerExecutor executor =
                        new SqlServerExecutor(ConfigurationManager.ConnectionStrings["IdentityDb"].ConnectionString))
                {
                    string query = $"USE {DbName} CREATE TABLE {AnonDbInfosTableName} ("
                                   + "ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, "
                                   + "NAME NVARCHAR(200) NOT NULL, "
                                   + "DATEOFCREATING DATETIME NOT NULL, "
                                   + "DATEOFDELETING DATETIME NOT NULL, "
                                   + "CONNECTIONSTRING NVARCHAR(500) NOT NULL, "
                                   + "DBMSTYPE NVARCHAR(128), "
                                   + ")";

                    executor.ExecuteQueryAsString(query);
                }
            }
        }
Пример #2
0
        /// <summary>
        /// Creates private database with selected login and password
        /// </summary>
        /// <param name="login"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public string CreateNewDatabaseWithProtection(string login, string password)
        {
            //errors collector
            StringBuilder result = new StringBuilder();

            var loginTimeStamp = DateTime.Now.Ticks % 100000;

            using (SqlServerExecutor executor = new SqlServerExecutor(_masterConnectionString))
            {
                string createLoginQuery = $"USE MASTER CREATE LOGIN {login + loginTimeStamp} WITH PASSWORD = '******'";
                result.Append(executor.ExecuteQueryAsString(createLoginQuery));

                string createDbQuery   = $"USE MASTER CREATE DATABASE {_dataBaseName}";
                string createUserQuery = $"USE {_dataBaseName} CREATE USER {login + loginTimeStamp} FOR LOGIN {login + loginTimeStamp}";
                //Apply protection rules
                string grantPermissionQuery = $"USE {_dataBaseName} EXEC sp_addrolemember 'db_owner', {login + loginTimeStamp}";

                result.Append(executor.ExecuteQueryAsString(createDbQuery));
                result.Append(executor.ExecuteQueryAsString(createUserQuery));
                result.Append(executor.ExecuteQueryAsString(grantPermissionQuery));
            }

            if (result.ToString() != string.Empty)
            {
                throw new Exception(result.ToString());
            }

            return($"Data Source={LocalSqlServerName};Initial Catalog={_dataBaseName};User Id={login + loginTimeStamp};Password={password}");
        }
Пример #3
0
        public void TestGetAlterTablesQueries()
        {
            var schema = new DatabaseSchema();

            schema.AlterTable("table1")
            .AddColumn("column1")
            .AddColumn("column2")
            .ModifyColumn("column3", ColumnType.Int())
            .ModifyColumn("column4", ColumnType.Int())
            .DropColumn("column5")
            .DropColumn("column6")
            .AddPrimaryKeyConstraint("id")
            .AddForeignKeyConstraint("table2_id", "table2", "id")
            .AddForeignKeyConstraint("table3_id", "table3", "id");

            var service = new SqlServerExecutor(schema);

            var result = service.GetAlterTablesQueries();

            Assert.AreEqual(9, result.Count);
            Assert.AreEqual("ALTER TABLE [dbo].[table1] ADD [column1] NVARCHAR(255) NOT NULL;\r", result[0]);
            Assert.AreEqual("ALTER TABLE [dbo].[table1] ADD [column2] NVARCHAR(255) NOT NULL;\r", result[1]);
            Assert.AreEqual("ALTER TABLE [dbo].[table1] ALTER COLUMN [column3] INT NOT NULL;\r", result[2]);
            Assert.AreEqual("ALTER TABLE [dbo].[table1] ALTER COLUMN [column4] INT NOT NULL;\r", result[3]);
            Assert.AreEqual("ALTER TABLE [dbo].[table1] DROP COLUMN [column5];\r", result[4]);
            Assert.AreEqual("ALTER TABLE [dbo].[table1] DROP COLUMN [column6];\r", result[5]);
            Assert.AreEqual("ALTER TABLE [dbo].[table1] ADD PRIMARY KEY ([id]);\r", result[6]);
            Assert.AreEqual("ALTER TABLE [dbo].[table1] ADD FOREIGN KEY ([table2_id]) REFERENCES [dbo].[table2]([id]);\r", result[7]);
            Assert.AreEqual("ALTER TABLE [dbo].[table1] ADD FOREIGN KEY ([table3_id]) REFERENCES [dbo].[table3]([id]);\r", result[8]);
        }
Пример #4
0
        public void TestSwitchDelimiter_WithSeed()
        {
            var schema = new DatabaseSchema();

            schema.SeedTable("table1")
            .Insert(SeedData.New.Set("id", 1).Set("column1", "value1"))
            .Insert(SeedData.New.Set("id", 2).Set("column1", "value2"));

            var service = new SqlServerExecutor(schema);

            var result = service.GetSeedQueriesWithSemicolonDelimiter();

            Assert.AreEqual(2, result.Count);
            Assert.AreEqual("INSERT INTO [dbo].[table1] ([id],[column1]) VALUES (1,'value1');\r", result[0]);
            Assert.AreEqual("INSERT INTO [dbo].[table1] ([id],[column1]) VALUES (2,'value2');\r", result[1]);

            var result2 = service.GetSeedQueryWithGODelimiter();

            Assert.AreEqual("INSERT INTO [dbo].[table1] ([id],[column1]) VALUES (1,'value1')\rGO\rINSERT INTO [dbo].[table1] ([id],[column1]) VALUES (2,'value2')\rGO\r", result2);

            var result3 = service.GetSeedQueriesWithSemicolonDelimiter();

            Assert.AreEqual(2, result.Count);
            Assert.AreEqual("INSERT INTO [dbo].[table1] ([id],[column1]) VALUES (1,'value1');\r", result3[0]);
            Assert.AreEqual("INSERT INTO [dbo].[table1] ([id],[column1]) VALUES (2,'value2');\r", result[1]);

            var result4 = service.GetSeedQueryWithGODelimiter();

            Assert.AreEqual("INSERT INTO [dbo].[table1] ([id],[column1]) VALUES (1,'value1')\rGO\rINSERT INTO [dbo].[table1] ([id],[column1]) VALUES (2,'value2')\rGO\r", result4);
        }
Пример #5
0
        public void TestGetCreateTablesQueries()
        {
            var schema = new DatabaseSchema();

            schema.CreateTable("table1")
            .AddPrimaryKey("id")
            .AddColumn("column1")
            .Insert(SeedData.New.Set("id", 1).Set("column1", "value1"))
            .Insert(SeedData.New.Set("id", 2).Set("column1", "value2"));

            schema.CreateTable("table2").AddPrimaryKey("id").AddColumn("column1").AddForeignKey("table1_id", "table1", "id");

            var service = new SqlServerExecutor(schema);

            var result = service.GetCreateTablesQueries();

            Assert.AreEqual(11, result.Count);
            Assert.AreEqual("DROP TABLE IF EXISTS [dbo].[table2];\r", result[0]);
            Assert.AreEqual("DROP TABLE IF EXISTS [dbo].[table1];\r", result[1]);
            Assert.AreEqual("CREATE TABLE [dbo].[table1] (\r\t[id] INT NOT NULL IDENTITY(1,1),\r\t[column1] NVARCHAR(255) NOT NULL\r);\r", result[2]);
            Assert.AreEqual("CREATE TABLE [dbo].[table2] (\r\t[id] INT NOT NULL IDENTITY(1,1),\r\t[column1] NVARCHAR(255) NOT NULL,\r\t[table1_id] INT NOT NULL\r);\r", result[3]);
            Assert.AreEqual("SET IDENTITY_INSERT [dbo].[table1] ON;\r", result[4]);
            Assert.AreEqual("INSERT INTO [dbo].[table1] ([id],[column1]) VALUES (1,'value1');\r", result[5]);
            Assert.AreEqual("INSERT INTO [dbo].[table1] ([id],[column1]) VALUES (2,'value2');\r", result[6]);
            Assert.AreEqual("SET IDENTITY_INSERT [dbo].[table1] OFF;\r", result[7]);
            Assert.AreEqual("ALTER TABLE [dbo].[table1] ADD PRIMARY KEY ([id]);\r", result[8]);
            Assert.AreEqual("ALTER TABLE [dbo].[table2] ADD PRIMARY KEY ([id]);\r", result[9]);
            Assert.AreEqual("ALTER TABLE [dbo].[table2] ADD FOREIGN KEY ([table1_id]) REFERENCES [dbo].[table1]([id]);\r", result[10]);
        }
Пример #6
0
        public static void RemoveAnonymousDbInfo(DataBaseInfo info)
        {
            string query = $"USE {DbName}  DELETE FROM {AnonDbInfosTableName} WHERE Id={info.Id};";

            using (
                SqlServerExecutor executor =
                    new SqlServerExecutor(ConfigurationManager.ConnectionStrings["IdentityDb"].ConnectionString))
            {
                executor.ExecuteQueryAsString(query);
            }
        }
Пример #7
0
        /// <summary>
        /// Checks if login exists
        /// </summary>
        /// <param name="login">login name</param>
        /// <returns></returns>
        public bool IsLoginExists(string login)
        {
            string    strQuery = $"USE MASTER SELECT [dbo].[LoginExists]('{login}') AS [exists]";
            DataTable dt;

            using (SqlServerExecutor executor = new SqlServerExecutor(_masterConnectionString))
            {
                dt = executor.ExecuteQueryAsDataTable(strQuery);
            }
            return((bool)dt.Rows[0]["exists"]);
        }
Пример #8
0
        public void TestSeederIsCalled()
        {
            var container = new SqlServerExecutor(new DatabaseSchema());

            _1_Seed.IsCalled = false;

            var file = new RecognizedMigrationFile(typeof(_1_Seed), "_1_Seed", "Seed", "_1");

            container.RunSeeder(file);

            Assert.IsTrue(_1_Seed.IsCalled);
        }
Пример #9
0
        public static string ExecuteQuery(string query, string connectionString, DbmsType type)
        {
            IQueryExecutor executor = null;

            switch (type)
            {
            case DbmsType.SqlServer:
                executor = new SqlServerExecutor(connectionString);
                break;
            }

            return(executor.ExecuteQueryAsString(query));
        }
Пример #10
0
        public void TestGetLast()
        {
            var container = new SqlServerExecutor(new DatabaseSchema());

            var files = new List <RecognizedMigrationFile> {
                new RecognizedMigrationFile(typeof(_1_Mig), "_1_Mig", "Mig", "_1"),
                new RecognizedMigrationFile(typeof(_2_Mig), "_2_Mig", "Mig", "_2"),
            };

            var result = container.GetLast(files);

            Assert.AreEqual("_2", result.Version);
        }
Пример #11
0
        public ActionResult QueryExecutor(QueryExecutorVm vm)
        {
            //Find user
            AppUser      user    = _userManager.FindById(User.Identity.GetUserId());
            DataBaseInfo foundDb = DataBasesManager.GetDbInfos(user.Id).Single(m => m.Id == vm.DbId);

            //TODO: Think, if i need different executors for each DBMS or not

            using (IQueryExecutor executor = new SqlServerExecutor(foundDb.ConnectionString))
            {
                vm.DataTable = executor.ExecuteQueryAsDataTable(vm.Query);
            }
            return(PartialView("QueryResults", vm.DataTable));
        }
Пример #12
0
        public void TestMigrationIsCalled_WithDown()
        {
            var container = new SqlServerExecutor(new DatabaseSchema());

            _1_Mig.UpIsCalled   = false;
            _1_Mig.DownIsCalled = false;

            var file = new RecognizedMigrationFile(typeof(_1_Mig), "_1_Mig", "Mig", "_1");

            container.RunMigration(file, MigrationDirection.Down);

            Assert.IsFalse(_1_Mig.UpIsCalled);
            Assert.IsTrue(_1_Mig.DownIsCalled);
        }
Пример #13
0
        private bool HasColumnDefaultValue(DataColumn column)
        {
            string query =
                $"SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{_dataTable.TableName}' AND COLUMN_NAME = '{column.ColumnName}'";
            DataTable      dtResult;
            IQueryExecutor exe;

            using (exe = new SqlServerExecutor(_dbInfo.ConnectionString))
            {
                dtResult = exe.ExecuteQueryAsDataTable(query);
            }

            return(dtResult.Rows[0][0].GetType() != typeof(DBNull));
        }
Пример #14
0
        /// <summary>
        /// Adds record about database to DbInfos.
        /// </summary>
        /// <param name="info"></param>
        public static void AddDbInfo(DataBaseInfo info)
        {
            string query = $"USE {DbName} INSERT INTO {DbInfosTableName} "
                           + "(NAME, DATEOFCREATING, CONNECTIONSTRING, DBMSTYPE, USERKEY) "
                           +
                           $"VALUES('{info.Name}', CONVERT(DATETIME, '{info.DateOfCreating.ToString("yyyy-MM-dd HH:mm:ss")}', 120), "
                           + $"'{info.ConnectionString}','{info.DbmsType}', '{info.ForeignKey}');";

            using (
                SqlServerExecutor executor =
                    new SqlServerExecutor(ConfigurationManager.ConnectionStrings["IdentityDb"].ConnectionString))
            {
                executor.ExecuteQueryAsString(query);
            }
        }
Пример #15
0
        public void TestGetCreateDatabasesQueries()
        {
            var schema = new DatabaseSchema();

            schema.CreateDatabase("db1");
            schema.CreateDatabase("db2");

            var service = new SqlServerExecutor(schema);

            var result = service.GetCreateDatabasesQueries();

            Assert.AreEqual(2, result.Count);
            Assert.AreEqual("CREATE DATABASE [db1];\r", result[0]);
            Assert.AreEqual("CREATE DATABASE [db2];\r", result[1]);
        }
Пример #16
0
        public void TestGetDropDatabasesQueries()
        {
            var schema = new DatabaseSchema();

            schema.DropDatabase("db1");
            schema.DropDatabase("db2");

            var service = new SqlServerExecutor(schema);

            var result = service.GetDropDatabasesQueries();

            Assert.AreEqual(2, result.Count);
            Assert.AreEqual("DROP DATABASE IF EXISTS [db1];\r", result[0]);
            Assert.AreEqual("DROP DATABASE IF EXISTS [db2];\r", result[1]);
        }
Пример #17
0
        public void TestGetDropStoredProceduresQueries()
        {
            var schema = new DatabaseSchema();

            schema.DropStoredProcedure("p1");
            schema.DropStoredProcedure("p2");

            var service = new SqlServerExecutor(schema);

            var result = service.GetDropStoredProceduresQueries();

            Assert.AreEqual(2, result.Count);
            Assert.AreEqual("DROP PROCEDURE IF EXISTS [dbo].[p1];\r", result[0]);
            Assert.AreEqual("DROP PROCEDURE IF EXISTS [dbo].[p2];\r", result[1]);
        }
Пример #18
0
        public void TestGetDropTablesQueries()
        {
            var schema = new DatabaseSchema();

            schema.DropTable("table1");
            schema.DropTable("table2");

            var service = new SqlServerExecutor(schema);

            var result = service.GetDropTablesQueries();

            Assert.AreEqual(2, result.Count);
            Assert.AreEqual("DROP TABLE IF EXISTS [dbo].[table1];\r", result[0]);
            Assert.AreEqual("DROP TABLE IF EXISTS [dbo].[table2];\r", result[1]);
        }
Пример #19
0
        public void TestDbServiceIsCalledOnQueries()
        {
            var dbService = new SqlFakeDbService();
            var container = new SqlServerExecutor(new MigrationAssemblyService(),
                                                  new SqlQueryService(),
                                                  dbService,
                                                  new DatabaseSchema());

            container.OpenConnectionAndExecuteQueries(new List <string> {
                "q1", "q2"
            }, "");

            Assert.AreEqual(2, dbService.Results.Count);
            Assert.AreEqual("q1", dbService.Results[0]);
            Assert.AreEqual("q2", dbService.Results[1]);
        }
Пример #20
0
        /// <summary>
        /// Drops database from SQL server
        /// </summary>
        /// <param name="dbName">Name of database</param>
        /// <returns></returns>
        public bool DropDataBase(string dbName)
        {
            string strQuery = $" USE MASTER ALTER DATABASE[{dbName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  DROP DATABASE[{dbName}];";

            string queryResult;

            using (SqlServerExecutor executor = new SqlServerExecutor(_masterConnectionString))
            {
                queryResult = executor.ExecuteQueryAsString(strQuery);
            }
            if (queryResult != string.Empty)
            {
                return(false);
            }

            return(true);
        }
Пример #21
0
        public void TestGetCreateStoredProceduresQueries()
        {
            var schema = new DatabaseSchema();

            schema.CreateStoredProcedure("p1").AddInParameter("p_id", ColumnType.Int()).SetBody("select * from users where id=p_id");
            schema.CreateStoredProcedure("p2").SetBody("select * from users");

            var service = new SqlServerExecutor(schema);

            var result = service.GetCreateStoredProceduresQueries();

            Assert.AreEqual(4, result.Count);
            Assert.AreEqual("DROP PROCEDURE IF EXISTS [dbo].[p1];\r", result[0]);
            Assert.AreEqual("CREATE PROCEDURE [dbo].[p1] p_id INT\rAS\rBEGIN\rselect * from users where id=p_id;\rEND;\r", result[1]);
            Assert.AreEqual("DROP PROCEDURE IF EXISTS [dbo].[p2];\r", result[2]);
            Assert.AreEqual("CREATE PROCEDURE [dbo].[p2] \rAS\rBEGIN\rselect * from users;\rEND;\r", result[3]);
        }
Пример #22
0
        public void TestGetSeedQuery()
        {
            var schema = new DatabaseSchema();

            schema.SeedTable("table1")
            .Insert(SeedData.New.Set("column1", "value1"))
            .Insert(SeedData.New.Set("column1", "value2"));

            schema.SeedTable("table2")
            .Insert(SeedData.New.Set("column1", "value1"))
            .Insert(SeedData.New.Set("column1", "value2"));

            var service = new SqlServerExecutor(schema);

            var result = service.GetSeedQueryWithGODelimiter();

            Assert.AreEqual("INSERT INTO [dbo].[table1] ([column1]) VALUES ('value1')\rGO\rINSERT INTO [dbo].[table1] ([column1]) VALUES ('value2')\rGO\r\rINSERT INTO [dbo].[table2] ([column1]) VALUES ('value1')\rGO\rINSERT INTO [dbo].[table2] ([column1]) VALUES ('value2')\rGO\r", result);
        }
Пример #23
0
        private static void ExecuteUpdate(string sql, GenerationOptions options)
        {
            if (string.IsNullOrEmpty(options.DbConnectionString))
            {
                throw new InvalidOperationException("ExecuteUpdate ist nur mit Datenbankverbindung zulässig");
            }

            if (options.GenerateMsSql)
            {
                using (IExecutor exec = new SqlServerExecutor(options.DbConnectionString, GENERATED_SQL))
                {
                    ExecuteUpdateInternal(options, exec);
                }
            }
            else if (options.GenerateMySql)
            {
                using (IExecutor exec = new MySqlExecutor(options.DbConnectionString, GENERATED_SQL))
                {
                    ExecuteUpdateInternal(options, exec);
                }
            }
        }
Пример #24
0
        public ActionResult QueryExecutorWithCs(QueryExecutorVm vm)
        {
            var connectionString = Request["conStr"].Trim();

            vm.DataTable = new DataTable();
            var errorDataColumn = new DataColumn("FastSqlQueryErrMessages", typeof(string));

            vm.DataTable.Columns.Add(errorDataColumn);

            //Check if connection string is empty
            if (string.IsNullOrEmpty(connectionString))
            {
                var row = vm.DataTable.NewRow();
                row[0] = "Ваша строка подключения пуста!";
                vm.DataTable.Rows.Add(row);
                return(PartialView("QueryResults", vm.DataTable));
            }
            //Try to establish connection
            IQueryExecutor executor = null;

            try
            {
                executor = new SqlServerExecutor(connectionString);
            }
            catch
            {
                var row = vm.DataTable.NewRow();
                row[0] = "Не удалось установить соеденение.";
                vm.DataTable.Rows.Add(row);
                executor?.Dispose();
                return(PartialView("QueryResults", vm.DataTable));
            }
            //receive data
            vm.DataTable = executor.ExecuteQueryAsDataTable(vm.Query);
            executor.Dispose();

            return(PartialView("QueryResults", vm.DataTable));
        }
Пример #25
0
        public void TestSwitchDelimiter()
        {
            var schema = new DatabaseSchema();

            schema.CreateTable("table1")
            .AddColumn("column1");

            schema.CreateStoredProcedure("p2").SetBody("select * from users");

            var service = new SqlServerExecutor(schema);

            // ;/r
            var result = service.GetMigrationQueriesWithSemicolonDelimiter(false);

            Assert.AreEqual(4, result.Count);
            Assert.AreEqual("DROP TABLE IF EXISTS [dbo].[table1];\r", result[0]);
            Assert.AreEqual("CREATE TABLE [dbo].[table1] (\r\t[column1] NVARCHAR(255) NOT NULL\r);\r", result[1]);
            Assert.AreEqual("DROP PROCEDURE IF EXISTS [dbo].[p2];\r", result[2]);
            Assert.AreEqual("CREATE PROCEDURE [dbo].[p2] \rAS\rBEGIN\rselect * from users;\rEND;\r", result[3]);

            // /rGO/r
            var result2 = service.GetMigrationQueryWithGODelimiter();

            Assert.AreEqual("DROP TABLE IF EXISTS [dbo].[table1]\rGO\r\rCREATE TABLE [dbo].[table1] (\r\t[column1] NVARCHAR(255) NOT NULL\r)\rGO\r\rDROP PROCEDURE IF EXISTS [dbo].[p2]\rGO\r\rCREATE PROCEDURE [dbo].[p2] \rAS\rBEGIN\rselect * from users;\rEND\rGO\r", result2);

            var result3 = service.GetMigrationQueriesWithSemicolonDelimiter(false);

            Assert.AreEqual(4, result3.Count);
            Assert.AreEqual("DROP TABLE IF EXISTS [dbo].[table1];\r", result3[0]);
            Assert.AreEqual("CREATE TABLE [dbo].[table1] (\r\t[column1] NVARCHAR(255) NOT NULL\r);\r", result3[1]);
            Assert.AreEqual("DROP PROCEDURE IF EXISTS [dbo].[p2];\r", result3[2]);
            Assert.AreEqual("CREATE PROCEDURE [dbo].[p2] \rAS\rBEGIN\rselect * from users;\rEND;\r", result3[3]);

            var result4 = service.GetMigrationQueryWithGODelimiter();

            Assert.AreEqual("DROP TABLE IF EXISTS [dbo].[table1]\rGO\r\rCREATE TABLE [dbo].[table1] (\r\t[column1] NVARCHAR(255) NOT NULL\r)\rGO\r\rDROP PROCEDURE IF EXISTS [dbo].[p2]\rGO\r\rCREATE PROCEDURE [dbo].[p2] \rAS\rBEGIN\rselect * from users;\rEND\rGO\r", result4);
        }
Пример #26
0
        public void TestGetMigrationQuery()
        {
            var schema = new DatabaseSchema();

            schema.CreateTable("table1")
            .AddPrimaryKey("id")
            .AddColumn("column1")
            .Insert(SeedData.New.Set("id", 1).Set("column1", "value1"))
            .Insert(SeedData.New.Set("id", 2).Set("column1", "value2"));

            schema.CreateTable("table2").AddPrimaryKey("id")
            .AddColumn("column1")
            .AddForeignKey("table1_id", "table1", "id")
            .Insert(SeedData.New.Set("id", 1).Set("column1", "value1"));

            schema.CreateStoredProcedure("p1").AddInParameter("p_id", ColumnType.Int()).SetBody("select * from users where id=p_id");
            schema.CreateStoredProcedure("p2").SetBody("select * from users");

            var service = new SqlServerExecutor(schema);

            var result = service.GetMigrationQueryWithGODelimiter();

            Assert.AreEqual("DROP TABLE IF EXISTS [dbo].[table2]\rGO\r\rDROP TABLE IF EXISTS [dbo].[table1]\rGO\r\rCREATE TABLE [dbo].[table1] (\r\t[id] INT NOT NULL IDENTITY(1,1),\r\t[column1] NVARCHAR(255) NOT NULL\r)\rGO\r\rCREATE TABLE [dbo].[table2] (\r\t[id] INT NOT NULL IDENTITY(1,1),\r\t[column1] NVARCHAR(255) NOT NULL,\r\t[table1_id] INT NOT NULL\r)\rGO\r\rSET IDENTITY_INSERT [dbo].[table1] ON\rGO\rINSERT INTO [dbo].[table1] ([id],[column1]) VALUES (1,'value1')\rGO\rINSERT INTO [dbo].[table1] ([id],[column1]) VALUES (2,'value2')\rGO\rSET IDENTITY_INSERT [dbo].[table1] OFF\rGO\r\rSET IDENTITY_INSERT [dbo].[table2] ON\rGO\rINSERT INTO [dbo].[table2] ([id],[column1]) VALUES (1,'value1')\rGO\rSET IDENTITY_INSERT [dbo].[table2] OFF\rGO\r\rALTER TABLE [dbo].[table1] ADD PRIMARY KEY ([id])\rGO\r\rALTER TABLE [dbo].[table2] ADD PRIMARY KEY ([id])\rGO\r\rALTER TABLE [dbo].[table2] ADD FOREIGN KEY ([table1_id]) REFERENCES [dbo].[table1]([id])\rGO\r\rDROP PROCEDURE IF EXISTS [dbo].[p1]\rGO\r\rCREATE PROCEDURE [dbo].[p1] p_id INT\rAS\rBEGIN\rselect * from users where id=p_id;\rEND\rGO\r\rDROP PROCEDURE IF EXISTS [dbo].[p2]\rGO\r\rCREATE PROCEDURE [dbo].[p2] \rAS\rBEGIN\rselect * from users;\rEND\rGO\r", result);
        }